parsing xml using tsql

crompnk
crompnk used Ask the Experts™
on
Hi,

I am trying to parse a snippet of xml using the following tsql. It is working without errors but I am getting null data rather than the values in double quotes:

DECLARE @x XML;

SET @x = '
<logger>
<TH date="20150730133052" id="th0" temp="21.9" hum="61" dew="14.0"/>
<WIND date="20150730133114" id="wind0" dir="0" gust="0.0" wind="0.0" chill="21.9"/>
<RAIN date="20150730133016" id="rain0" rate="0.0" total="0.0" delta="0.0"/>
<THB date="20150730133040" id="thb0" temp="24.0" hum="53" dew="13.8" press="1021.7" seapress="1022.0" fc="2"/>
</logger>';

SELECT tbl.TH.value('date[1]', 'varchar(100)') AS [date]
	,tbl.TH.value('id[1]', 'varchar(100)') AS id
	,tbl.TH.value('temp[1]', 'varchar(100)') AS temp
	,tbl.TH.value('hum[1]', 'varchar(100)') AS hum
	,tbl.TH.value('dew[1]', 'varchar(100)') AS dew
FROM @x.nodes('/logger/TH') AS tbl(TH);

Open in new window


Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
It's cause you want the attributes, not elements below <th>. Thus you need to query them:

SELECT  tbl.TH.value('@date', 'VARCHAR(100)') AS [date] ,
        tbl.TH.value('@id', 'VARCHAR(100)') AS id ,
        tbl.TH.value('@temp', 'FLOAT') AS temp ,
        tbl.TH.value('@hum', 'INT') AS hum ,
        tbl.TH.value('@dew', 'FLOAT') AS dew
FROM    @x.nodes('/logger/TH') AS tbl ( TH );

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial