XML Query with variable | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

XML Query with variable

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