12

I have some xml and need to extract values using sql

<?xml version="1.0" ?>  
<fields>  

<field name="fld_AccomAttic">  
<value>0</value>  
</field>  
<field name="fld_AccomBathroom">  
<value>1</value>  
</field>
</fields>  

</xml>

I need to get column name fld_AccomAttic
Value 1

The xml is held in a sql server 2005 db

I have used xquery before and it has worked.

How to extract these values?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Steven
  • 2,148
  • 8
  • 33
  • 50

2 Answers2

20
SELECT <xmlfield>.value('(/xml/fields/field/@name)[1]', 'varchar(60)')
FROM <table>
WHERE <xmlfield>.value('(/xml/fields/field/value/)[1], 'int') = 1

Replace with your table and field names.

Paul Talbot
  • 1,583
  • 1
  • 14
  • 28
  • I was hoping to achive a row view of the XML I.e fld_AccomAttic fld_AccomBathroom Value 0 0 Is this possible P.s many thanks for your original response Sp – Steven May 26 '10 at 10:51
  • 1
    This is the best example of this I have seen on the web. Can anyone point me to a good reference or tutorial that explains this? – Dan Bailiff Mar 25 '11 at 16:14
7

Figured it matey

XMLData.value('(/xml/fields/field[@name = "fld_AccomAttic"]/value)[1]','varchar(50)')
Cœur
  • 37,241
  • 25
  • 195
  • 267
Steven
  • 2,148
  • 8
  • 33
  • 50