How to read formated xml file in SQL Server

Question

I have XML file that need to read some data from it by SQL Server 2008.

Please guide me to to solve this problem.

My XML file like that:

<?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="CI_CODE" type="xs:decimal" minOccurs="0" />
                <xs:element name="CI_NAME" type="xs:string" minOccurs="0" />
                <xs:element name="CI_PISH_CODE" 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">
        <CI_CODE>1</CI_CODE>
        <CI_NAME>Kerman</CI_NAME>
        <CI_PISH_CODE>34</CI_PISH_CODE>
      </Table>
      <Table diffgr:id="Table2" msdata:rowOrder="1">
        <CI_CODE>2</CI_CODE>
        <CI_NAME>Anar</CI_NAME>
        <CI_PISH_CODE>34</CI_PISH_CODE>
      </Table>
      <Table diffgr:id="Table3" msdata:rowOrder="2">
        <CI_CODE>3</CI_CODE>
        <CI_NAME>Baft</CI_NAME>
        <CI_PISH_CODE>34</CI_PISH_CODE>
      </Table>
    </NewDataSet>
  </diffgr:diffgram>
</DataSet>

I need to help to read this XML in SQL Server 2008.

I need tags <CI_CODE> and <CI_NAME> and <CI_PISH_CODE> data.

Please guide me

Answer 1

In general I'd advise you to be as specific as possible. In this case you have unclean namespaces (empty default namespace at schema level), which makes it difficult to query this correctly.

Try it wild wildcarded namespace (*:):

EDIT: simplified

--Your XML in a variable

DECLARE @xml XML=
'Copy your XML here';

--The query

SELECT dgt.value('(@*:id)[1]','nvarchar(max)') AS DiffgramTable
      ,dgt.value('(*:CI_CODE)[1]','int') AS Diffgram_CI_CODE
      ,dgt.value('(*:CI_NAME)[1]','nvarchar(max)') AS Diffgram_CI_NAME
      ,dgt.value('(*:CI_PISH_CODE)[1]','int') AS Diffgram_CI_CODE
FROM @xml.nodes('/*:DataSet') AS A(ds)
OUTER APPLY ds.nodes('*:diffgram') AS C(dg)
OUTER APPLY dg.nodes('*:NewDataSet/*:Table') AS D(dgt)

First we pick the "DataSet" and call it "ds". Below this we pick "diffgram" ("dg"). Below "dg" we pick all "Table" ("dgt")

The select will read all information out of the nearest node

The result

+---------------+------------------+------------------+------------------+
| DiffgramTable | Diffgram_CI_CODE | Diffgram_CI_NAME | Diffgram_CI_CODE |
+---------------+------------------+------------------+------------------+
| Table1        | 1                | Kerman           | 34               |
+---------------+------------------+------------------+------------------+
| Table2        | 2                | Anar             | 34               |
+---------------+------------------+------------------+------------------+
| Table3        | 3                | Baft             | 34               |
+---------------+------------------+------------------+------------------+

Answer 2

Assuming your XML is stored in a variable called @Data XML, you can use this XQuery go get your data.

The XPath expression "navigates" down the tree of nodes to give you a "virtual" table of XML fragments - one for each <Table> XML element. From that XML element, you can then fetch the individual data items using the .value() XQuery expression.

You need to be especially vigilant about using the correct XML namespaces for each element.

;WITH XMLNAMESPACES('http://tempuri.org/' AS ns, 
                    'urn:schemas-microsoft-com:xml-diffgram-v1' AS dg)
SELECT
    CI_CODE = XC.value('(CI_CODE)[1]', 'int'),
    CI_NAME = XC.value('(CI_NAME)[1]', 'varchar(50)'),
    CI_PISH_CODE = XC.value('(CI_PISH_CODE)[1]', 'int')
FROM
    @Data.nodes('/ns:DataSet/dg:diffgram/NewDataSet/Table') AS XT(XC)

This produces this output on my PC: