How I build XQuery for this?

1
votes

hellow I want to select from my table T the XML column X
with XQuery or by other ways?

two values of the column X for example, in T table look like this:

row 1

<bts>
   <bt id="1">RAY</TextValue>
   <bt id="2">רוי</TextValue>
   <bt id="3">Ré</TextValue>
</bts>

row 2

<bts>
   <bt id="1">DAN</TextValue>
   <bt id="2">דן</TextValue>
   <bt id="3">Dé</TextValue>
</bts>

then I want to select all the rows from the table where the id of the element
in the X column is @a (for example @a = 3)
and put all the text in temp table that include them in NVARCHAR type. 

Ré
Dé

if @a will be equal to 2 then the i want that the temp table will be:

רוי
דן

HOW I DO IT INT T-SQL ???

1
sql-servertsqlxquery
Your closing </TextValue> doesn't match the opening <bt> tag.... - marc_s

1 Answers

0
votes

Try something like this:

DECLARE @WhichValue INT
SET @WhichValue = 1  -- or 2, or whatever

SELECT 
   X.value('(/bts/bt[@id=sql:variable("@WhichValue")]/text())[1]', 'NVARCHAR(50)')
FROM 
   dbo.T