dynamic xml value - 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)

dynamic xml value

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-12-08 : 03:06:33

Hi

I have this piece of query that needs some tweaking....


Declare @TheName Char(50)
SET @TheName = 'sLine1'

SELECT t.u.value('(sLine1/text())[1]', 'nvarchar(30)') as [Namnet], tbl.ID

FROM tbl_Order tbl
CROSS APPLY XmlFormValues.nodes('/root')t(u) 
WHERE     (tbl.ID = 18025)

How can I replace the t.u.value('(sLine1 part with the @TheName?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-08 : 03:42:56

One way is to use dynamic sql but it got its own pros and cons.

http://www.sommarskog.se/dynamic_sql.html

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-08 : 03:46:06

Well, there is a workaround and it looks like this.
It's not the most perormant way to solve this but it works.



DECLARE @Sample TABLE
	(
		ID INT,
		XmlFormValues XML
	)

INSERT	@Sample
SELECT	18025, N'<root><sLine1>Peso</sLine1><sLine2>Yak</sLine2></root>'

DECLARE	@theElement NVARCHAR(MAX)
SET	@theElement = 'sLine2'

SELECT		t.u.value('(text())[1]', 'NVARCHAR(30)') AS Namnet,
		tbl.ID
FROM		@Sample AS tbl
CROSS APPLY	XmlFormValues.nodes('/root/*') AS t(u) 
WHERE		tbl.ID = 18025
		AND t.u.exist('local-name(.) [. = sql:variable("@theElement")]') = 1

Also see http://weblogs.sqlteam.com/peterl/archive/2008/03/26/Some-XML-search-approaches.aspx
and http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx
and http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx
and http://weblogs.sqlteam.com/peterl/archive/2009/08/04/Manipulate-XML-data-continued.aspx
for more ways to handle XML.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-08 : 03:51:46

Here is a quick way to do the search and absolutely no need for dynamic sql!


DECLARE @Sample TABLE
	(
		ID INT,
		XmlFormValues XML
	)

INSERT	@Sample
SELECT	18025, N'<root><sLine1>Peso</sLine1><sLine2>Yak</sLine2><a><sLine1>MVP</sLine1></a></root>'

DECLARE	@theElement NVARCHAR(MAX)
SET	@theElement = 'sLine1'

SELECT		t.u.value('(text())[1]', 'NVARCHAR(30)') AS Namnet,
		tbl.ID
FROM		@Sample AS tbl
CROSS APPLY	XmlFormValues.nodes('/root/* [local-name(.) = sql:variable("@theElement")]') AS t(u) 
WHERE		tbl.ID = 18025

Notice that there are two <sLine1> elements (second is on another level).

N 56°04'39.26"
E 12°55'05.63"

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-12-08 : 05:38:04

Hi Peso

If I run your sample code it works just fine, but if I use this code...



Declare @theElement Char(50)
SET @theElement = 'sLine1'

SELECT		t.u.value('(text())[1]', 'NVARCHAR(30)') AS Namnet,
		tbl.ID
FROM tbl_Order tbl
CROSS APPLY	XmlFormValues.nodes('/root/* [local-name(.) = sql:variable("@theElement")]') AS t(u) 
WHERE		tbl.ID = 18025

I don't get any errors, but I don't get any results either. Any ideas of what might ne wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-08 : 06:21:40

XML is case sensiTivE. Also, change the declaration of @theElement to VARCHAR(50).

N 56°04'39.26"
E 12°55'05.63"

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-12-08 : 06:29:31

Excellent, that did it. Thanks!

Sachin.Nand

2937 Posts

Posted - 2010-12-08 : 06:38:48

Another way using Exists method



create table #t(id int,t xml)
insert into #t
select 18025,
N'<root><sLine1>Peso</sLine1><sLine2>Yak</sLine2><a><sLine1>MVP</sLine1></a></root>'
DECLARE @value VARCHAR(20)
SET @value ='MVP'

if exists(Select #t.t.exist('/root/*[text()=sql:variable("@value")]')from #t)
	Select * from(
	Select id,x.i.value('.','varchar(20)')value from #t t1
	cross apply t1.t.nodes('/root/*')x(i)
)T where value=@value

drop table #t

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-08 : 06:40:39

quote:
Originally posted by magmo

Excellent, that did it. Thanks!

Varsågod!

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources