XML data type query - 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

General SQL Server Forums

New to SQL Server Programming

XML data type query

Author

Topic

cmessineo
Starting Member

1 Post

Posted - 2009-04-15 : 09:42:21

Hi all, I have a xml column with the following data:

<benchmarks><classid>1021</classid><classid>1011</classid></benchmarks>

I want to get all classid's from the xml via a query and join the classid on another table, but i am stumped.

I tried

Select Class.BenchMarks.query(
' for $c in /benchmarks
return $c/classid')
From Class where Classid = 8011

But all I get from that query is

<classid>1021</classid><classid>1011</classid>

Hopfuly someone can point me in the right direction.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 09:49:01

[code]DECLARE @Sample TABLE
(
ID INT PRIMARY KEY CLUSTERED,
data XML
)

INSERT @Sample
SELECT 1, '<benchmarks><classid>1021</classid><classid>1011</classid></benchmarks>'

SELECT ID,
g.value('.', 'INT') AS ClassID
FROM @Sample AS s
CROSS APPLY data.nodes('/benchmarks/classid') AS f(g)[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 10:10:31

And if you want the ClassID's to be indexed for faster processing when joining

CREATE TABLE	#Sample
		(
			ID INT PRIMARY KEY CLUSTERED,
			data XML
		)

INSERT	#Sample
SELECT	1, '<benchmarks><classid>1021</classid><classid>1011</classid></benchmarks>'

CREATE PRIMARY
XML INDEX	PXML_ClassID
ON		#Sample
		(
			data
		)

SELECT		ID,
		g.value('.', 'INT') AS ClassID
FROM		#Sample
CROSS APPLY	data.nodes('/benchmarks/classid') AS f(g)

DROP TABLE	#Sample

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources