How to parse XML nodes in SQL Server by attribute value

Question

Recently I faced a problem with parsing XML file via T-SQL script (in SQL Server 2008 R2).

The task is to retrieve data (indicators) on certain date. I managed to retrieve the data on all dates.

But I need to represent it like

|Date | Indicator_name | Indicator_value|

using select statement.

Later I will use this select in a cursor for update of several tables based on data retrieved from XML.

Can you help with the SQL and Xpath to use for correct output?

My code:

declare @in as xml; --original document with indicators
--test data for xml  
 set @in='
<root>
 <org name="org1"/>
  <dates>
   <date id="7/1/2009">
    <indicator1 name="110_ii">0</indicator1>
    <indicator1 name="1120_ii">0</indicator1>
    <indicator1 name="1121_ii">0</indicator1>
    <indicator1 name="1122_ii">0</indicator1>
    <indicator1 name="120_ii">4388176</indicator1>
    <indicator1 name="135_ii">0</indicator1>
    <indicator1 name="140_ii">866212</indicator1>
    <indicator1 name="145_ii">0</indicator1>
    <indicator1 name="150_ii">860428</indicator1>
    <indicator1 name="190_ii">6114816</indicator1>
    <indicator1 name="210_ii">286254</indicator1>
    <indicator1 name="220_ii">110173</indicator1>
    <indicator1 name="240_ii">707265</indicator1>
    <indicator1 name="250_ii">30115</indicator1>
    <indicator1 name="260_ii">378524</indicator1>
    <indicator1 name="270_ii">119324</indicator1>
    <indicator1 name="290_ii">1631655</indicator1>
    <indicator1 name="300_ii">7746471</indicator1>
    <indicator1 name="410_ii">325194</indicator1>
    <indicator1 name="411_ii">655</indicator1>
    <indicator1 name="1340_ii">0</indicator1>
    <indicator1 name="420_ii">251639</indicator1>
    <indicator1 name="430_ii">0</indicator1>
    <indicator1 name="470_ii">4601840</indicator1>
    <indicator1 name="490_ii">5178018</indicator1>
    <indicator1 name="510_ii">1204181</indicator1>
    <indicator1 name="515_ii">285692</indicator1>
    <indicator1 name="1430_ii">0</indicator1>
    <indicator1 name="520_ii">113460</indicator1>
    <indicator1 name="590_ii">1603333</indicator1>
    <indicator1 name="610_ii">508631</indicator1>
    <indicator1 name="620_ii">456489</indicator1>
    <indicator1 name="640_ii">0</indicator1>
    <indicator1 name="650_ii">0</indicator1>
    <indicator1 name="660_ii">0</indicator1>
    <indicator1 name="690_ii">965120</indicator1>
    <indicator1 name="700_ii">7746471</indicator1>
    <indicator1 name="910_ii">245294</indicator1>
    <indicator1 name="911_ii">165164</indicator1>
    <indicator1 name="920_ii">194742</indicator1>
    <indicator1 name="930_ii">0</indicator1>
    <indicator1 name="940_ii">524989</indicator1>
    <indicator1 name="950_ii">0</indicator1>
    <indicator1 name="960_ii">3647627</indicator1>
    <indicator1 name="970_ii">5377</indicator1>
    <indicator1 name="980_ii">0</indicator1>
    <indicator1 name="990_ii">0</indicator1>
    <indicator2 name="f2_010">1639698</indicator2>
    <indicator2 name="f2_020">1207761</indicator2>
    <indicator2 name="nacenka">0</indicator2>
    <indicator2 name="f2_021">0</indicator2>
    <indicator2 name="f2_022">106342</indicator2>
    <indicator2 name="f2_023">0</indicator2>
    <indicator2 name="f2_024">0</indicator2>
    <indicator2 name="f2_025">0</indicator2>
    <indicator2 name="f2_029">431937</indicator2>
    <indicator2 name="f2_030">0</indicator2>
    <indicator2 name="f2_040">0</indicator2>
    <indicator2 name="f2_050">431937</indicator2>
    <indicator2 name="f2_080">15790</indicator2>
    <indicator2 name="f2_060">15798</indicator2>
    <indicator2 name="f2_070">36105</indicator2>
    <indicator2 name="f2_090">0</indicator2>
    <indicator2 name="f2_100">39647</indicator2>
    <indicator2 name="f2_140">387773</indicator2>
    <indicator2 name="f2_150">88393</indicator2>
    <indicator2 name="f2_2421">0</indicator2>
    <indicator2 name="f2_142">0</indicator2>
    <indicator2 name="f2_141">6380</indicator2>
    <indicator2 name="f2_151">0</indicator2>
    <indicator2 name="f2_190">305760</indicator2>
    <indicator2 name="f2_2510">0</indicator2>
    <indicator2 name="f2_2520">0</indicator2>
    <indicator2 name="f2_2500">0</indicator2>
    <indicator2 name="f2_202">0</indicator2>
    <indicator2 name="f2_2910">0</indicator2>
   </date>
   <date id="8/1/2009">
    <indicator1 name="110_ii">0</indicator1>
    <indicator1 name="1120_ii">0</indicator1>    
   </date>
  </dates>
 </root>'

--here I have the select that just returns all indicators name and values on all dates. 
--I guess that XPath is needed in where section smth like node(//date[@balans_date])='8/1/2009'

select 
     coalesce(t.c.value('(@name)[1]', 'varchar(max)'),'0') as indicator_name
     ,coalesce(t.c.value('(.)[1]', 'decimal(28,4)'),0) as indicator_value
from @in.nodes('/root/dates/date/*') t(c)

Alexander · Answer 1 · 2013-04-01T14:49:44.550

1

You can use following expression in xpath to apply filtering by date:

select 
     coalesce(t.c.value('(../@id)[1]', 'datetime'), 0) as indicator_date,
     coalesce(t.c.value('(@name)[1]', 'varchar(max)'),'0') as indicator_name,
     coalesce(t.c.value('(.)[1]', 'decimal(28,4)'),0) as indicator_value
from @in.nodes('/root/dates/date[@id="8/1/2009"]/*') t(c)

edited Apr 01 '13 at 14:49

answered Apr 01 '13 at 13:54

Alexander

4,153
1
24
37

Yes, this works. Is it possible not to filter but to output the XML structure like ** date|indicator_name|indicator_value ** Or may be I need to create an array of dates from XML (in this example there are 2 dates, but can be many) and then retrieve data in cycle by dates? How can create an array and to add values to it from `select (t.c.value('(.)', 'nvarchar(255)')) from @in.nodes('/root/dates/date/@id') t(c)` – user2232196 Apr 01 '13 at 14:21
1

Great thanks! I change it a bit so that it retrieve the way I wanted. final version declare @in as xml; --original document with indicators --test data for xml `select coalesce(t.c.value('(../@id)[1]', 'datetime'), 0) as indicator_date, coalesce(t.c.value('(@name)[1]', 'varchar(max)'),'0') as indicator_name, coalesce(t.c.value('(.)[1]', 'decimal(28,4)'),0) as indicator_value from @in.nodes('/root/dates/date/*') t(c)` – user2232196 Apr 01 '13 at 15:06

How to parse XML nodes in SQL Server by attribute value

1 Answers1