Yousef Rahimy Akhondzadeh Yousef Rahimy Akhondzadeh - 2 months ago 21
SQL Question

How to read formated xml file in SQL Server

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

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:

enter image description here