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

Answer 1

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: