Convert XML column to Table Data in SQL Query

Question

I have this XML file which is a returned data table from web service:

        DECLARE @MyXML XML = 
        '<?xml version="1.0" encoding="utf-8"?>
        <DataTable xmlns="http://tempuri.org/">
          <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
            <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Blah" msdata:UseCurrentLocale="true">
              <xs:complexType>
                <xs:choice minOccurs="0" maxOccurs="unbounded">
                  <xs:element name="Blah">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element name="Col1" type="xs:string" minOccurs="0" />
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:choice>
              </xs:complexType>
            </xs:element>
          </xs:schema>
          <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
            <DocumentElement xmlns="">
              <Blah diffgr:id="Blah1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
                <Col1>testing1</Col1>
              </Blah>
            </DocumentElement>
          </diffgr:diffgram>
        </DataTable>'

I want an output like this: Select Col1

Col1

testing1

Then, I would be able to insert the data into a table in sql.

Gottfried Lesigang · Accepted Answer · 2018-08-06 10:44:00Z

Is this XML under your control?

I'm especially irritated by this

<DocumentElement xmlns="">

This line is redefining the default namespace.

The XML you show consists of a schema portion and a data portion

DECLARE @MyXML XML = 
'<?xml version="1.0" encoding="utf-8"?>
<DataTable xmlns="http://tempuri.org/">

    <!-- This schema is describing your data''s structure -->

    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Blah" msdata:UseCurrentLocale="true">
        <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
            <xs:element name="Blah">
            <xs:complexType>
                <xs:sequence>
                <xs:element name="Col1" type="xs:string" minOccurs="0" />
                </xs:sequence>
            </xs:complexType>
            </xs:element>
        </xs:choice>
        </xs:complexType>
    </xs:element>
    </xs:schema>

    <!-- End of schema -->
    <!-- Begin of data -->

    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <DocumentElement xmlns="">
        <Blah diffgr:id="Blah1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
        <Col1>testing1</Col1>
        </Blah>
    </DocumentElement>
    </diffgr:diffgram>

    <!-- End of data -->

</DataTable>';

Your statement

Then, I would be able to insert the data into a table in sql.

... let's me think, that the real-world use case will consist of more than one column. In this case you do not show enough.

This query would read just the <Col1> content in a lazy approach:

SELECT @MyXML.value('(//*:Col1/text())[1]','nvarchar(max)');

In general it is good to be as specific as possible, but the actual issue looks, as if you might be better off with a namespace wildcard and a deep search (//) down to <DocumentElement>:

SELECT de.query('.')
FROM @MyXml.nodes('//DocumentElement') A(de)

This query will return various content of this area:

SELECT de.value('(Blah/@*:id)[1]','nvarchar(max)') DiffGr_ID
      ,de.value('(Blah/@*:rowOrder)[1]','int') MsData_RowOrder
      ,de.value('(Blah/@*:hasChanges)[1]','nvarchar(max)') diffgr_HasChanges
      ,de.value('(Blah/Col1/text())[1]','nvarchar(max)') Col1_Text
FROM @MyXml.nodes('//DocumentElement') A(de);

The result

DiffGr_ID   MsData_RowOrder diffgr_HasChanges   Col1_Text
-----------------------------------------------------------
Blah1       0               inserted            testing1

Can you help me about this question? stackoverflow.com/questions/51719134/… Thank you.

Vic · Accepted Answer · 2018-08-06 07:44:35Z

1

INSERT INTO sampletable
    (
        col1,
        col2
    )
    SELECT
        t.value('(col1/text())[1]', 'nvarchar(10)') ---colum name of the xml
        t.value('(col2/text())[1]', 'nvarchar(10)') ---colum name of the xml
    FROM @xmlData.nodes('/NewDataSet/MYTABLE') AS TempTable(t)

take not ,when the type of the column in the xml is float you need to change the 'narchar(10)' to 'float', or if date use 'date', etc..

answered Aug 6, 2018 at 7:44

Vic

5371 gold badge7 silver badges28 bronze badges

Comments

dee.ronin · Accepted Answer · 2018-08-06 08:41:38Z

1

You need to set the XML Namespace before using the XQuery:

;WITH XMLNAMESPACES('http://tempuri.org/' AS ns, 'urn:schemas-microsoft-com:xml-diffgram-v1' AS dg)

SELECT
    Col1 = XC.value('(Col1)[1]', 'varchar(50)')
FROM
    @MyXML.nodes('/ns:DataTable/dg:diffgram/DocumentElement/Blah') AS XT(XC)

answered Aug 6, 2018 at 8:41

dee.ronin

1,03812 silver badges22 bronze badges

Collectives™ on Stack Overflow

Convert XML column to Table Data in SQL Query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related