Thursday, September 24, 2009

Query to select records from Xml

I have following Xml in Job table which column name is JobXml (Xml datatype)

<Job>
<TimeID Value="2323" id="a"/>
<TimeID Value="4445" id="b"/>
<TimeID Value="3453" id="a"/>
<TimeID Value="7677" id="c"/>
</Job>

I want to write a select query who returns all TimeID Value where id= "a" like this

2323
3453

Following is the query for that:

SELECT r.value('@Value','int')
FROM Job
CROSS APPLY StatisticsXml.nodes('/Job/TimeID[@id=''a'']') AS x(r)

No comments:

Post a Comment