SQL Server 2005 - searching for value in XML field

Question

Hello,

I'm trying to query a particular value in an XML field. I've seen lots of examples, but they don't seem to be what I'm looking for

Supposing my xml field is called XMLAttributes and table TableName, and the complete xml value is like the below:

<Attribute name="First2Digits" value="12" />
<Attribute name="PurchaseXXXUniqueID" value="U4RV123456762MBE79" />

(although the xml field will frequently have other attributes, not just PurchaseXXXUniqueID)

If I'm looking for a specific value in the PurchaseXXXUniqueID attribute name - say U4RV123456762MBE79 - how would I write the query? I believe it would be something like:

select * 
  from TableName
 where XMLAttributes.value('(/path/to/tag)[1]', 'varchar(100)') = '5FTZP2QT8Z3E2MAV2D'

... but it's the path/to/tag that I need to figure out.

Or probably there's other ways of getting the values I want.

To summarize - I need to get all the records in a table where the value of a particular attribute in the xml field matches a value I'll pass to the query.

thanks for the help! Sylvia

edit: I was trying to make this simpler, but in case it makes a difference - ultimately I'll have a temporary table of 50 or so potential values for the PurchaseXXXUniqueID field. For these, I want to get all the matching records from the table with the XML field.

Answer 1

Something like that?

declare @PurchaseXXXUniqueID varchar(max)
set @PurchaseXXXUniqueID = 'U4RV123456762MBE79';

select * from TableName t
where XMLAttributes.exist('//Attribute/@value = sql:variable("@PurchaseXXXUniqueID")') = 1

Answer 2

This ought to work:

SELECT 
    (fields from base table),
    Nodes.Attr.value('(@name)[1]', 'varchar(100)'),
    Nodes.Attr.value('(@value)[1]', 'varchar(100)')
FROM 
    dbo.TableName
CROSS APPLY
    XMLAttributes.nodes('/Attribute') AS Nodes(Attr)
WHERE
    Nodes.Attr.value('(@name)[1]', 'varchar(100)') = 'PurchaseXXXUniqueID'
    AND Nodes.Attr.value('(@value)[1]', 'varchar(100)') = 'U4RV123456762MBE79'

You basically need to join the base table's row against one "pseudo-row" for each of the <Attribute> nodes inside the XML column, and the pick out the individual attribute values from the <Attribute> node to select what you're looking for.