Unable to get Value from XML

  • bryan 83518

    Valued Member

    Points: 61

    <?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: 6008

    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: 182438

    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

    Valued Member

    Points: 61

    Thanks everyone both solutions work perfectly

    Bryan

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply