Insert XML file into SQL Server

Question

I'm trying to import an XML file into a SQL table. I found a few examples of code to do this, but I can't seem to get it to work. I've tried a few variations in my code but at this point I'm not sure if the issue is the XML file structure or my SQL.

Below is the code I'm using as well as the XML file (truncated to one record).

CREATE TABLE workspace.dbo.tbt_SED_XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)    

INSERT INTO workspace.dbo.tbt_SED_XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'File.xml', SINGLE_BLOB) AS x;


DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)    

SELECT @XML = XMLData FROM workspace.dbo.tbt_SED_XMLwithOpenXML WHERE ID =     '1' -- The row to process    

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


INSERT INTO workspace.dbo.tb_SED_Emails
SELECT email
FROM OPENXML(@hDoc, 'responseData/manifest/contact_data')
WITH 
(
email [varchar](128) 'email'
)


EXEC sp_xml_removedocument @hDoc
GO

XML File Example:

<?xml version="1.0" encoding="utf-8"?>
<methodResponse>
<item>
    <methodName>
        <![CDATA[]]>
    </methodName>
    <responseData>
        <manifest>
            <contact_data>
                <email>[email protected]</email>
            </contact_data>
        </manifest>
    </responseData>
    <responseNum>
        <![CDATA[1]]>
    </responseNum>
    <responseCode>
        <![CDATA[]]>
    </responseCode>
</item>
</methodResponse>

1) where is the structure of workspace.dbo.tb_SED_Emails ??? ............. 2) type the path instead of file name .. ex: d:\File.xml instead of File.xml — ahmed abdelqader
– ahmed abdelqader, Commented Nov 3, 2016 at 1:47

marc_s · Accepted Answer · 2016-11-03 06:18:47Z

3

Try to use the built-in, native XQuery support instead of the clunky old OPENXML stuff:

SELECT
    Email = XC.value('(email)[1]', 'varchar(255)')
FROM 
    workspace.dbo.tbt_SED_XMLwithOpenXML
CROSS APPLY 
    XMLData.nodes('/methodResponse/item/responseData/manifest/contact_data') AS XT(XC)

That should output the desired e-mail address for you:

answered Nov 3, 2016 at 6:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Eriksson · Accepted Answer · 2016-11-03 06:20:07Z

0

You are using the wrong xPath expression.

Change 'responseData/manifest/contact_data' to 'methodResponse/item/responseData/manifest/contact_data'.

answered Nov 3, 2016 at 6:20

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

Insert XML file into SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related