XML Query with variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML Query with variable

Author

Topic

karbon
Starting Member

9 Posts

Posted - 2007-01-12 : 06:42:02

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

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

karbon
Starting Member

9 Posts

Posted - 2007-01-12 : 07:55:34

i found the solution from ranjithain
[url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18865[/url]

Thanks to all ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources