SQL Server Performance

XML Query with variable

Discussion in 'SQL Server 2005 General Developer Questions' started by cemuney, Jan 12, 2007.

  1. cemuney New Member

    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 />
  2. ranjitjain New Member

    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
  3. cemuney New Member

    Thank you very very much ranjithain.
  4. ranjitjain New Member

    You are Welcome<img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page