CODEWITHSUNDEEP
sqlxmlt-sqlxpath
pencilCake
pencilCake

Reputation: 53323

How can I SELECT rows by using xpath on an XML column?

I have a table with a column named Description that contains XML like:

<Parent>
  <Child Target="A" Id="123456">
       <SpecialCode Target="I"  Value="2530138" />
  </Child>
</Parent>

If I want to set a parameter called @value that represents the Value attribute in this xml,

How can I SELECT the rows from MyTable where the Description column has a SpecialCode attribute that is equal to @value ?

DECLARE @value varchar(20);
SET @value = '44555'
SELECT ....
FROM MyTable

Upvotes: 2

Views: 1058

Answers (1)

marc_s
marc_s

Reputation: 755421

How about:

SELECT (list of columns)
FROM dbo.MyTable
WHERE [Description].exist('/Parent/Child/SpecialCode[@Value=sql:variable("@value")]') = 1

If your XML column is in a different column, then you'll use :

SELECT (list of columns)
FROM dbo.MyTable t1
INNER JOIN dbo.MyTable t2 ON t1.ID = t2.ID
WHERE t2.[Description].exist('/Parent/Child/SpecialCode[@Value=sql:variable("@value")]') = 1

Just alias your tables, properly join them, and prepend your XML column with the appropriate table alias....

Upvotes: 3

Related Questions