XML import into SQL Server using OPENROWSET and OPENXML

Question

Have never done this before, seem to be having issues with namespaces? Any help would be appreciated. If I remove the xmlns attributes from my XML file it works fine...

Sample XML:

<?xml version="1.0" encoding="UTF-8"?>
<ETS xsi:schemaLocation="http://www.caodc.ca/ETS/v3 ETS_v3.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.caodc.ca/ETS/v3">
<WellTours>
<WellTour>
<LicenseNo>001</LicenseNo>
<WellName>75-998</WellName>
</WellTour>
<WellTour>
<LicenseNo>007</LicenseNo>
<WellName>14-172</WellName>
</WellTour>
</WellTours>
</ETS>

Here is my SQL:

DECLARE @xml xml

SELECT @xml=I
FROM OPENROWSET (BULK 'C:\CCTESTFILE.XML', SINGLE_BLOB) as ImportFile(I)

SELECT @xml

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

SELECT *
FROM OPENXML (@hdoc, '/ETS/WellTours/WellTour',2)
WITH (
        WellName varchar(100),
        LicenseNo varchar(100));

EXEC sp_xml_removedocument @hdoc

marc_s · Accepted Answer · 2016-08-22 20:53:07Z

3

Much simpler to just use the built-in XQuery functionality instead of the old, bulky and memory-leaking OPENXML approach:

;WITH XMLNAMESPACES(DEFAULT 'http://www.caodc.ca/ETS/v3')
SELECT
    LicenseNo = XC.value('(LicenseNo)[1]', 'varchar(10)'),
    WellName = XC.value('(WellName)[1]', 'varchar(25)')
FROM
    @xml.nodes('/ETS/WellTours/WellTour') AS XT(XC)

Gives me an output of:

answered Aug 22, 2016 at 20:53

marc_s

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

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

1 Comment

marc_s Over a year ago

@C-COOP: if you feel this answer helped you solve your problem, then please accept this answer. This will show your appreciation for the people who spent their own time to help you.

Alex Kudryashev · Accepted Answer · 2016-08-22 21:43:08Z

If for some reason you have to use openxml then you need to add namespace declaration to sp_xml_preparedocument call. Something like this.

declare @xml varchar(max)= --no need to declare as xml
'<?xml version="1.0" encoding="UTF-8"?>
<ETS xsi:schemaLocation="http://www.caodc.ca/ETS/v3 ETS_v3.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.caodc.ca/ETS/v3">
<WellTours>
<WellTour>
<LicenseNo>001</LicenseNo>
<WellName>75-998</WellName>
</WellTour>
<WellTour>
<LicenseNo>007</LicenseNo>
<WellName>14-172</WellName>
</WellTour>
</WellTours>
</ETS>'

DECLARE @hdoc int

--Note x: before ETS and :x after xmlns
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml, '<x:ETS xmlns:x="http://www.caodc.ca/ETS/v3" />'

--Note x: before elements
SELECT *
FROM OPENXML (@hdoc, 'x:ETS/x:WellTours/x:WellTour',2)
WITH (
        WellName varchar(100) 'x:WellName',
        LicenseNo varchar(100) 'x:LicenseNo');

EXEC sp_xml_removedocument @hdoc

Collectives™ on Stack Overflow

XML import into SQL Server using OPENROWSET and OPENXML

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related