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?
Answer
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.