XML QUERY with variable

• Cem Uney

  Mr or Mrs. 500

  Points: 533

  More actions

  January 12, 2007 at 4:53 am

  #171755

   

  Hi everyone

  I have a table with XML.

  And i want to query it by @id variable.

  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>

  '

  I can easily query table like below.

   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

  But i have a problem with

  WHERE  nref.exist('id[. = "2"]') = 1 [:0]

  this line.

  I want to make <<id>> column as variable.

  @id.

  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

  how can i get the result of this query???

  I want result of exec(@sSQL)[]

  Thanks all

• Cem Uney

  Mr or Mrs. 500

  Points: 533

  More actions

  January 12, 2007 at 6:01 am

  #682876

   

  i found  the solution from ranjithain

  http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18865

  Thanks to all