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==