Unable to get Value from XML

• bryan 83518

  SSC Journeyman

  Points: 91

  More actions

  July 24, 2014 at 12:12 pm

  #288888

  <?xml version="1.0" encoding="utf-8"?>

  <DataSet 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:UseCurrentLocale="true">

  <xs:complexType>

  <xs:choice minOccurs="0" maxOccurs="unbounded">

  <xs:element name="Table">

  <xs:complexType>

  <xs:sequence>

  <xs:element name="KeyID" type="xs:string" minOccurs="0" />

  <xs:element name="LicenseAcquisitionUrl" type="xs:string" minOccurs="0" />

  <xs:element name="CHECKSUM" type="xs:string" minOccurs="0" />

  <xs:element name="ContentID" 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">

  <NewDataSet xmlns="">

  <Table diffgr:id="Table1" msdata:rowOrder="0">

  <KeyID>ABA38156-D357-4C2A-933A-838604EE4F83</KeyID>

  <LicenseAcquisitionUrl>http://ready.steady.com/preauth.aspx?pX=0DF3E4</LicenseAcquisitionUrl&gt;

  <CHECKSUM>0A38b6k3kiQ=</CHECKSUM>

  <ContentID>zqFuE60mxIQEWEzYyxoMkw==</ContentID>

  </Table>

  </NewDataSet>

  </diffgr:diffgram>

  </DataSet>

  I have this XML coming from a third party website I am trying to get the KeyID and ContentId values using xquery but it always returns NULL does anybody know how to work with this type of XML.

  Bryan

• Arthur Olcot

  SSCertifiable

  Points: 6639

  More actions

  July 24, 2014 at 1:07 pm

  #1732183

  Hi, you can do it by using a namespace. Here is a not so elegant way of getting the value out:

  DECLARE @xml XML = '<?xml version="1.0" encoding="utf-8"?>

  <DataSet 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:UseCurrentLocale="true">

  <xs:complexType>

  <xs:choice minOccurs="0" maxOccurs="unbounded">

  <xs:element name="Table">

  <xs:complexType>

  <xs:sequence>

  <xs:element name="KeyID" type="xs:string" minOccurs="0" />

  <xs:element name="LicenseAcquisitionUrl" type="xs:string" minOccurs="0" />

  <xs:element name="CHECKSUM" type="xs:string" minOccurs="0" />

  <xs:element name="ContentID" 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">

  <NewDataSet xmlns="">

  <Table diffgr:id="Table1" msdata:rowOrder="0">

  <KeyID>ABA38156-D357-4C2A-933A-838604EE4F83</KeyID>

  <LicenseAcquisitionUrl>http://ready.steady.com/preauth.aspx?pX=0DF3E4</LicenseAcquisitionUrl&gt;

  <CHECKSUM>0A38b6k3kiQ=</CHECKSUM>

  <ContentID>zqFuE60mxIQEWEzYyxoMkw==</ContentID>

  </Table>

  </NewDataSet>

  </diffgr:diffgram>

  </DataSet>

  '

  ;

  WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/')

  SELECT @xml.value('(//DataSet/*:diffgram/*:NewDataSet/*:Table/*:KeyID/text())[1]', 'UNIQUEIDENTIFIER')

  , @xml.value('(//DataSet/*:diffgram/*:NewDataSet/*:Table/*:ContentID/text())[1]', 'VARCHAR(24)')

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  July 24, 2014 at 1:24 pm

  #1732187

  Slightly different flavour of the same

  😎

  DECLARE @TXML XML = '<?xml version="1.0" encoding="utf-8"?>

  <DataSet 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:UseCurrentLocale="true">

  <xs:complexType>

  <xs:choice minOccurs="0" maxOccurs="unbounded">

  <xs:element name="Table">

  <xs:complexType>

  <xs:sequence>

  <xs:element name="KeyID" type="xs:string" minOccurs="0" />

  <xs:element name="LicenseAcquisitionUrl" type="xs:string" minOccurs="0" />

  <xs:element name="CHECKSUM" type="xs:string" minOccurs="0" />

  <xs:element name="ContentID" 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">

  <NewDataSet xmlns="">

  <Table diffgr:id="Table1" msdata:rowOrder="0">

  <KeyID>ABA38156-D357-4C2A-933A-838604EE4F83</KeyID>

  <LicenseAcquisitionUrl>http://ready.steady.com/preauth.aspx?pX=0DF3E4</LicenseAcquisitionUrl&gt;

  <CHECKSUM>0A38b6k3kiQ=</CHECKSUM>

  <ContentID>zqFuE60mxIQEWEzYyxoMkw==</ContentID>

  </Table>

  </NewDataSet>

  </diffgr:diffgram>

  </DataSet>'

  ;WITH XMLNAMESPACES (

  'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr

  ,DEFAULT 'http://tempuri.org/')

  SELECT

  DIFF1.GRAM.value('*:KeyID[1]','VARCHAR(50)') AS KeyID

  ,DIFF1.GRAM.value('*:LicenseAcquisitionUrl[1]','VARCHAR(50)') AS LicenseAcquisitionUrl

  ,DIFF1.GRAM.value('*:CHECKSUM[1]','VARCHAR(50)') AS [CHECKSUM]

  ,DIFF1.GRAM.value('*:ContentID[1]','VARCHAR(50)') AS ContentID

  FROM @TXML.nodes('DataSet') AS DATAS(ET)

  OUTER APPLY DATAS.ET.nodes('diffgr:diffgram/*:NewDataSet/*:Table') AS DIFF1(GRAM)

  Results

  KeyID LicenseAcquisitionUrl CHECKSUM ContentID

  ------------------------------------- ----------------------------------------------- -------------- ------------------------

  ABA38156-D357-4C2A-933A-838604EE4F83 http://ready.steady.com/preauth.aspx?pX=0DF3E4 0A38b6k3kiQ= zqFuE60mxIQEWEzYyxoMkw==

• bryan 83518

  SSC Journeyman

  Points: 91

  More actions

  July 24, 2014 at 8:56 pm

  #1732239

  Thanks everyone both solutions work perfectly

  Bryan