xml and getting a list of values

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 and getting a list of values

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-08-05 : 04:11:02

i am using this example http://msdn.microsoft.com/en-us/library/ms186918.aspx to read an xml.
is there a way, if i have some node like this :
<x>1</x>
<x>41</x>
<x>61</x>
to get all the nodes into a single list, withoguht the need to go over each node an add it to a diffrent varibale?

Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 04:19:05

Is this what you want?

DECLARE	@Sample XML

SET	@Sample = '
			<x>1</x>
			<x>41</x>
			<x>61</x>
		'
		
SELECT	n.value('.', 'INT') AS theValue
FROM	@Sample.nodes('x') AS r(n)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 04:21:03

Or this, on a single line?

DECLARE	@Sample XML

SET	@Sample = '
			<x>1</x>
			<x>41</x>
			<x>61</x>
		'
		
SELECT	n.value('.', 'INT') AS theValue
FROM	@Sample.nodes('.') AS r(n)

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-08-05 : 05:38:25

your almost there.
what do i do if the XML more coplicated, for example :
<a>
<b>
<x>1</x>
<x>2</x>
<x>243</x>
</b>
</a>

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 05:41:07

You didn't try anything for yourself?

DECLARE	@Sample XML

SET	@Sample = '
<a>
<b>
<x>1</x>
<x>2</x>
<x>243</x>
</b>
</a>

		'
		
SELECT	n.value('.', 'INT') AS theValue
FROM	@Sample.nodes('/a/b/x') AS r(n)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 05:41:41

Or simply

DECLARE	@Sample XML

SET	@Sample = '
<a>
<b>
<x>1</x>
<x>2</x>
<x>243</x>
</b>
</a>

		'
		
SELECT	n.value('.', 'INT') AS theValue
FROM	@Sample.nodes('//x') AS r(n)

to get all elements named "x" whereever they are.

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-08-05 : 07:17:22

how do i insert a result list like in your code, to a variable?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 09:01:07

Do you mind showing us in what expected format or result you want?
I have been on 4 weeks of vacation. My mindreading capabilities are low right now.

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-08-05 : 09:03:28

ok sorry your corrent
i managed it whith putting the data into a temp table
Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources