SQL Server Performance Forum – Threads Archive

XML Query with variable

Hi everyone I have a table with XML. And i want to query it by @id variable. <pre id="code"> CREATE Table test(Filter XML) insert into test select ' <root> <book> <id>1</id> <name>SQLSERVER</name> <authors> <string>John</string> <string>Michael</string> <string>Jane</string> </authors> </book> <book> <id>2</id> <name>SSAnalysisServer</name> <authors> <string>Abraham</string> <string>Suzan</string> </authors> </book> </root> ' </pre id="code"> I can easily query table like below. <pre id="code"> DECLARE @resultXML xml select @resultXML = nref.query(‘authors’) FROM test CROSS APPLY Filter.nodes(‘//root/book’) AS R(nref) WHERE nref.exist(‘id[. = "2"]’) = 1 Select @resultXML </pre id="code"> But i have a problem with WHERE nref.exist(‘id[. = "2"]’) = 1 [:0] this line. I want to make <<id>> column as variable. @id. <pre id="code"> declare @id varchar(10) declare @sSQL varchar(1000) set @id=’1′ set @sSql= ’DECLARE @resultXML xml select @resultXML = nref.query(”authors”) FROM test CROSS APPLY Filter.nodes(”//root/book”) AS R(nref) WHERE nref.exist(”id[. = "@id"]”) = 1′ set @sSql = replace(@sSQL,’@id’,@id) select @sSql </pre id="code"> how can i get the result of this query??? I want result of exec(@sSQL)[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] Thanks all

Hi,
You can restrict the ids that are returned as part of WHERE clause and parameterise that.
Consider this: DECLARE @resultXML xml
declare @id int
set @id=2
select @resultXML = nref.query(‘authors’)FROM test
CROSS APPLY Filter.nodes(‘//root/book’) AS R(nref)
WHERE nref.value(‘(id)[1]’,’int’) = @id
Select @resultXML

Thank you very very much ranjithain.

You are Welcome<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

]]>