Read XML in SQL Server

Question

I am trying to read below XML in SQL. But I am always getting null values.

I saved my xml in table EMPweb and then try to read XML. My SQL code is as shown below. Please help if you can.

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetEmployeeResponse xmlns="http://tempuri.org/">
      <GetEmployeeResult>
        <xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="Plant">
          <xs:element name="Plant" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
            <xs:complexType>
              <xs:choice minOccurs="0" maxOccurs="unbounded">
                <xs:element name="OrderReleaseAblity">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element name="BusinessEntityID" type="xs:int" minOccurs="0" />
                      <xs:element name="NationalIDNumber" type="xs:string" minOccurs="0" />
                      <xs:element name="LoginID" type="xs:string" minOccurs="0" />
                      <xs:element name="JobTitle" 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">
          <Plant xmlns="">
            <OrderReleaseAblity diffgr:id="OrderReleaseAblity1" msdata:rowOrder="0">
              <BusinessEntityID>275</BusinessEntityID>
              <NationalIDNumber>841560125</NationalIDNumber>
              <LoginID>adventure-works\michael9</LoginID>
              <JobTitle>Sales Representative</JobTitle>
            </OrderReleaseAblity>
          </Plant>
        </diffgr:diffgram>
      </GetEmployeeResult>
    </GetEmployeeResponse>
  </soap:Body>
</soap:Envelope>

SQL query:

DECLARE @xDoc XML ; 
Set @xDoc= (Select Data_xml from EMPWeb);

;WITH  XMLNAMESPACES('urn:schemas-microsoft-com:xml-msdata' as msdata,'urn:schemas-microsoft-com:xml-diffgram-v1' as diffgr,default 'http://www.w3.org/2001/XMLSchema'  )

select T.X.value('JobTitle[1]','varchar(500)') AS JobTitle

from @xDoc.nodes('GetEmployeeResponse/GetEmployeeResult/diffgr:diffgram/Plant/OrderReleaseAblity') as T(X)

you didn't mention sql code

Pasindu
– Pasindu

2018-06-10 16:45:20 +00:00
Commented Jun 10, 2018 at 16:45 — Pasindu
– Pasindu, Commented Jun 10, 2018 at 16:45
You also didn't include the XML.

Dan Guzman
– Dan Guzman

2018-06-10 16:47:42 +00:00
Commented Jun 10, 2018 at 16:47 — Dan Guzman
– Dan Guzman, Commented Jun 10, 2018 at 16:47

marc_s · Accepted Answer · 2018-06-10 17:20:38Z

1

You got your XML namespaces and the XPath in the .nodes() call wrong - try this:

;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap, 
                    'http://tempuri.org/' AS ns, 
                    'urn:schemas-microsoft-com:xml-diffgram-v1' AS dg)
SELECT 
    T.X.value('JobTitle[1]','varchar(500)') AS JobTitle
FROM 
    @xDoc.nodes('/soap:Envelope/soap:Body/ns:GetEmployeeResponse/ns:GetEmployeeResult/dg:diffgram/Plant/OrderReleaseAblity') AS T(X)

answered Jun 10, 2018 at 17:20

marc_s

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

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

Collectives™ on Stack Overflow

Read XML in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related