Search XML Column in SQL

You may have to play with the nodes bit to get it exact.

SELECT    y.item.value('@UnitID', 'int') AS UnitID,    y.item.value('@Name', 'varchar(100)') AS [Name],    y.item.value('@SName', 'varchar(100)') AS [SName]FROM    <table>    CROSS APPLY    XMLDoc.nodes('/root') AS y(item)WHERE    y.item.value('@UnitID', 'int') = 2

Edit: corrected code to use table, not xml local variable

You have plenty of ways of getting it. "gbn" showed one way - two other are here.

If you want the whole "row" (I assumed you'll put these things into a tag), try this:

select    xmldoc.query('//node[@UnitID="2"]')from    xmltest

If you want just the "Name" attribute from the tag, use this:

select    xmldoc.value('(//node[@UnitID="2"]/@Name)[1]', 'varchar(20)')from    xmltest

If you need to access a whole bunch of attributes and/or subelements, use gbn's approach with the "CROSS APPLY xmldoc.nodes(....)".

Enjoy! XML Support in SQL Server 2005 is really quite extensive and useful!

Marc

SELECT * FROM CourseXMLWHERE XMLDoc = 'UnitID="2"'

Isn't that it? Or am I misunderstanding something?