Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to get Value from XML Expand / Collapse
Author
Message
Posted Thursday, July 24, 2014 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:55 PM
Points: 3, Visits: 24
<?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>
<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
Post #1595981
Posted Thursday, July 24, 2014 1:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:46 AM
Points: 2,644, Visits: 1,651
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>
<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)')

Post #1595998
Posted Thursday, July 24, 2014 1:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 2,253, Visits: 6,172
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>
<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==
Post #1596004
Posted Thursday, July 24, 2014 8:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:55 PM
Points: 3, Visits: 24
Thanks everyone both solutions work perfectly

Bryan
Post #1596071
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse