Simple XQUERY data retrieval - 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 2008 Forums

Transact-SQL (2008)

Simple XQUERY data retrieval

Author

Topic

andrewhopkinson
Yak Posting Veteran

63 Posts

Posted - 2010-04-27 : 11:30:50

So let's say I have this xml data:

<Root><a>111</a>235<a>222</a>4444</Root>

How can I use xquery to retrieve the value for the element <a></a> where the value for element is 4444?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 11:53:05

your a and b are at same levels. are you sure this is correct xml format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-27 : 14:45:41

[code]declare @data xml = '<Root><a>111</a>235<a>222</a>4444</Root>'

;with cteA(element, recid)
AS (
select t.value('.', 'int'),
row_number() OVER (order by t)
from @data.nodes('/Root/a') AS r(t)
), cteB(element, recid)
AS (
select t.value('.', 'int'),
row_number() OVER (order by t)
from @data.nodes('/Root/b') AS r(t)
)
SELECT a.element
FROM cteA AS a
INNER JOIN cteB AS b ON b.recID = a.recID
WHERE b.element = 4444[/code]

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

andrewhopkinson
Yak Posting Veteran

63 Posts

Posted - 2010-04-28 : 14:53:37

that's crazy! but it works, thank you.

as an added bonus, recID tells me what level I'm on!

andrewhopkinson
Yak Posting Veteran

63 Posts

Posted - 2010-04-28 : 16:31:06

Hey Peso, I have another problem, is there anyway that I can use the recID field to dynamically update a node in an xml satement? For instance, something like this

DECLARE @nodeIndex int
DECLARE @newvalue int

SET @newvalue = 9999

SET @nodeIndex = (SELECT a.recID
FROM cteA AS a
INNER JOIN cteB AS b ON b.recID = a.recID
WHERE b.element = 4444)

SET @data.modify('
replace value of (/Root/a[sql:variable("@nodeIndex")]/text())[1]
with sql:variable("@newvalue")')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-28 : 16:58:06

Works for me

declare @data xml = '<Root><a>111</a><b>235</b><a>222</a><b>4444</b></Root>'

select	@data

DECLARE @nodeIndex int

;with cteA(element, recid)
AS (
	select	t.value('.', 'int'),
		row_number() OVER (order by t)
	from	@data.nodes('/Root/a') AS r(t)
), cteB(element, recid)
AS (
	select	t.value('.', 'int'),
		row_number() OVER (order by t)
	from	@data.nodes('/Root/b') AS r(t)
)
SELECT		@NodeIndex = a.recID
FROM		cteA AS a
INNER JOIN	cteB AS b ON b.recID = a.recID
WHERE		b.element = 4444

SELECT	@NodeIndex

DECLARE @newvalue int = 123456789

SET @data.modify('
replace value of (/Root/a[sql:variable("@NodeIndex")]/text())[1]
with sql:variable("@newvalue")')

SELECT	@data

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

andrewhopkinson
Yak Posting Veteran

63 Posts

Posted - 2010-04-29 : 10:54:14

Okay, you can do that, but you can't do this:

SET @somevalue = @data.value('(/Root/a)[sql:variable("@NodeIndex")]','nvarchar(100)')

But if the dynamic modify works with your code, I might not need to do this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-29 : 14:10:02

Use query instead.

declare @data xml = '<Root><a>111</a><b>235</b><a>222</a><b>4444</b></Root>'

declare @NodeIndex tinyint = 2
select	@data.query('(/Root/a[sql:variable("@NodeIndex")])/text()') 

set @NodeIndex = 1
select	@data.query('(/Root/a[sql:variable("@NodeIndex")])/text()')

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