SQL Server 2005 - finding a value in an XML field

Question

I am trying to request a specific value in an XML field. I have seen many examples, but they do not seem to be what I am looking for.

Suppose my xml field is called XMLAttributes and table TableName, and the full xml value is similar to the following:

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

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

If I search for a specific value in the attribute name PurchaseXXXUniqueID - say, U4RV123456762MBE79 - how would I write a query? I believe this will be something like:

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

... but this is the / to / tag path I need to figure out.

Or maybe there are other ways to get the values ​​I want.

To summarize - I need to get all the records in the table where the value of a particular attribute in the xml field corresponds to the value that I will pass to the request.

thanks for the help! Sylvia

edit: , , - 50 PurchaseXXXUniqueID. XML.

Answer 1

:

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'

"-" <Attribute> XML <Attribute> node, .

Answer 2

- ?

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

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