Yousef Rahimy Akhondzadeh Yousef Rahimy Akhondzadeh - 1 year ago 112
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="">
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<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" />
<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">
<Table diffgr:id="Table2" msdata:rowOrder="1">
<Table diffgr:id="Table3" msdata:rowOrder="2">

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

I need tags

Please guide me

Answer Source

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.

                    'urn:schemas-microsoft-com:xml-diffgram-v1' AS dg)
    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')
    @Data.nodes('/ns:DataSet/dg:diffgram/NewDataSet/Table') AS XT(XC)

This produces this output on my PC:

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download