extract datav values for XML column with XML namespaces in SQL Server

Question

Can anybody please help me with the below xml. I need extract all the xml values like below.

AwarYear   Comments                         FieldCode   FieldNumber  Key    Value
AY2013-14  AAI: Adjusted Available Income   AAI            306       Blank  None Calculated

Here is the sample XML.

<SchemaType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/process">
  <AwardYear>AY2013_14</AwardYear>
  <Fields>
    <FieldSchema>
      <Comments>AAI: Adjusted Available Income</Comments>
      <DbLocation>IsirData</DbLocation>
      <FieldCode>AAI</FieldCode>
      <FieldNumber>306</FieldNumber>
      <ReportDisplay>Data</ReportDisplay>
      <ValidContent>
       <ValidValueContent xmlns:d5p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
          <d5p1:KeyValueOfstringstring>
            <d5p1:Key>Blank</d5p1:Key>
            <d5p1:Value>None calculated</d5p1:Value>
          </d5p1:KeyValueOfstringstring>
        </ValidValueContent>
      </ValidContent>
    </FieldSchema>
      </Fields>
</SchemaType>

Please do the need full. Thanks in advance.

Answer 1

Assuming you have your XML in a table inside an XML column like this:

DECLARE @XmlTable TABLE (ID INT NOT NULL, XMLDATA XML)

INSERT INTO @XmlTable VALUES(1, '<SchemaType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/process">
  <AwardYear>AY2013_14</AwardYear>
  <Fields>
    <FieldSchema>
      <Comments>AAI: Adjusted Available Income</Comments>
      <DbLocation>IsirData</DbLocation>
      <FieldCode>AAI</FieldCode>
      <FieldNumber>306</FieldNumber>
      <ReportDisplay>Data</ReportDisplay>
      <ValidContent>
       <ValidValueContent xmlns:d5p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
          <d5p1:KeyValueOfstringstring>
            <d5p1:Key>Blank</d5p1:Key>
            <d5p1:Value>None calculated</d5p1:Value>
          </d5p1:KeyValueOfstringstring>
        </ValidValueContent>
      </ValidContent>
    </FieldSchema>
      </Fields>
</SchemaType>')

then you can use this T-SQL statement to fetch the values:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/process', 
                    'http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS ns1)
SELECT
    AwardYear = XmlData.value('(SchemaType/AwardYear)[1]', 'varchar(25)'),
    Comments = XmlData.value('(SchemaType/Fields/FieldSchema/Comments)[1]', 'varchar(50)'),
    FieldCode = XmlData.value('(SchemaType/Fields/FieldSchema/FieldCode)[1]', 'varchar(10)'),
    FieldNumber = XmlData.value('(SchemaType/Fields/FieldSchema/FieldNumber)[1]', 'int'),
    [Key] = XmlData.value('(SchemaType/Fields/FieldSchema/ValidContent/ValidValueContent/ns1:KeyValueOfstringstring/ns1:Key)[1]', 'varchar(10)'),
    [Value] = XmlData.value('(SchemaType/Fields/FieldSchema/ValidContent/ValidValueContent/ns1:KeyValueOfstringstring/ns1:Value)[1]', 'varchar(10)')
FROM
    @XmlTable

I defined the top-level XML namespace as the "default" namespace (that doesn't need to be referenced all over the place), and the second namespace deep inside your structure is defined explicitly with a separate XML namespace prefix.