select from a XML 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)

select from a XML variable...

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2008-07-21 : 03:07:56

My xml value looks like this..

declare @list xml
select @list =
'<AOS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<strval>5</strval>
<strval>2</strval>
<strval>3</strval>
<strval>4</strval>
<strval>7</strval>
</AOS>'

both my queries return only the 1st value... (i.e. 5)

SELECT @list.value('(AOS/strval)[1]','int') as 'Id'

SELECT t.tmp.value('(./strval)[1]','int') as 'Id'
FROM @list.nodes('AOS') t(tmp)

I want all the values to be listed.
Please tell me, where the issue lies...

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-21 : 03:42:48

Hi ...
chk this...

declare @list xml
select @list =
'<AOS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<strval>5</strval>
<strval>2</strval>
<strval>3</strval>
<strval>4</strval>
<strval>7</strval>
</AOS>'
select @list
select x.i.value('.','varchar(50)') id
from @list.nodes('//strval')x(i)

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-21 : 03:48:34

Cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources