Xquery get value from attribute

Question

Hi,

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.

Can any one show me how to extract these values

Im baffeled as to why i am unable to do this

Thanks Sp

Answer 1

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.

Answer 2

Figured it matey XMLData.value('(/xml/fields/field[@name = "fld_AccomAttic"]/value)[1]','varchar(50)')

Many Thanks for your help

Much appriciated Sp