Monday, June 21, 2010

XQuery - Projecting Attributes With a Where Clause a Query

Had to write an xquery yesterday to extract some attribute values from an xml column, so I thought of posting it here for future reference.

This the structure of my XML data stored in the column xmlColumn


This is the records that I have in my table







What I need is to select the Id column from a normal table column and then the "value" attributes from the xml coulumn whose "SiteText" is equal to 1

This is the query written in 2 different ways

select id, xmlColumn.
query('data(AuditRecord/Records/Record[@SiteText=1]/@value)')
from temp

select id, xmlColumn.
value('data(AuditRecord/Records/Record[@SiteText=1]/@value)[1]', 'varchar(50)')
from temp

and this is the result set I get









1 comment: