Database

DECLARE @xml XML

SET @xml = '<DataSet>
  <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="T">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="ID" type="xs:int" minOccurs="0" />
                <xs:element name="NodeGroup" type="xs:string" minOccurs="0" />
                <xs:element name="NodeCount" type="xs:int" 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>
      <T diffgr:id="T1" msdata:rowOrder="0">
        <ID>648</ID>
        <NodeGroup>Moustord</NodeGroup>
        <NodeCount>5</NodeCount>
      </T>
      <T diffgr:id="T2" msdata:rowOrder="1">
        <ID>646</ID>
        <NodeGroup>Shell</NodeGroup>
        <NodeCount>9</NodeCount>
      </T>
      <T diffgr:id="T3" msdata:rowOrder="2">
        <ID>647</ID>
        <NodeGroup>Suze</NodeGroup>
        <NodeCount>4</NodeCount>
      </T>
    </NewDataSet>
  </diffgr:diffgram>
</DataSet>'

 
;WITH XMLNAMESPACES( 
        'http://www.w3.org/2001/XMLSchema' AS ns,
        'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr )
SELECT 
        fd.c.value('(ID/text())[1]', 'INT') 'a',
        fd.c.value('(NodeGroup/text())[1]', 'nvarchar(255)') 'b',
        fd.c.value('(NodeCount/text())[1]', 'INT') 'c'
        
FROM @xml.nodes('ns:DataSet/diffgr:diffgram/NewDataSet/T') fd(c)