Import XML into SQL server using OPENXML command with XMLNS

sql, sql-server, xml

Solution

I would recommend to skip the `OPENXML` stuff altogether and use the built-in, native XQuery support in SQL Server:

declare @input XML = '<PportTimetable xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" timetableID="20161018020822" xmlns="http://www.thalesgroup.com/rtti/XmlTimetable/v8">
    <Journey rid="201610188012733" uid="P12733" trainId="2J27" ssd="2016-10-18" toc="AW">
    </Journey>
</PportTimetable>'

-- define your XML namespaces - here, there's only a single "default" namespace    
;WITH XMLNAMESPACES(DEFAULT 'http://www.thalesgroup.com/rtti/XmlTimetable/v8')
SELECT 
    RID = XC.value('@rid', 'varchar(50)'),
    UID = XC.value('@uid', 'varchar(20)'),
    TrainId = XC.value('@trainId', 'varchar(25)'),
    SSD = XC.value('@ssd', 'varchar(25)'),
    TOC = XC.value('@toc', 'varchar(20)')
FROM 
    @input.nodes('/PportTimetable/Journey') AS XT(XC)

Use the XQuery `.nodes()` function to "shred" your XML into an "inline" table of XML fragments (one for each `<Journey>` node, in this example), and then use the `.value()` function to grab the individual elements and attributes from those XML fragments one by one.

Problem

I have the following code to import an xml into SQL ``` DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX) SELECT @XML = XMLData FROM XMLwithOpenXML EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML SELECT rid, uid FROM OPENXML(@hDoc, '/PportTimetable/Journey') WITH ( rid [varchar](50) '@rid', uid [varchar](100) '@uid' ) EXEC sp_xml_removedocument @hDoc GO ``` I can get the code to work but only when it does not contain the xmlns information as seen below why is this? xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.thalesgroup.com/rtti/XmlTimetable/v8" XML header ``` <PportTimetable xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" timetableID="20161018020822" xmlns="http://www.thalesgroup.com/rtti/XmlTimetable/v8"> <Journey rid="201610188012733" uid="P12733" trainId="2J27" ssd="2016-10-18" toc="AW"> </Journey> </PportTimetable> ```

Original source