read xml values - 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)

read xml values

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 10:24:48

Hi

I have the following xml structure in a xml data type column..


<collection>
  <entry>
    <artist>Depeche mode</artist>
    <country></country>
    <records>
      <album rating="5">Speak and spell</album>
      <album rating="10">Greatest hits</album>
    </records>
  <entry>  
</collection>

Can someone please show me how I can retrive the artist element (Depeche Mode) from a query

And also retrieve the 2 different album values, if the rating attribute also could be returned it would be great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:32:06

[code]
DECLARE @XMLData xml
SET @XMLData='<collection>
<entry>
<artist>Depeche mode</artist>
<country></country>
<records>
<album rating="5">Speak and spell</album>
<album rating="10">Greatest hits</album>
</records>
<entry>
</collection>'

SELECT t.u.value('.','varchar(100)') AS album,
t.u.value('./@rating','int') AS rating,
t.u.value('//artist','varchar(100)') AS artist
FROM @XMLData.nodes('/collection/entry/album')t(u)
[/code]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 10:34:29

Hi visakh16

I get this error when running the above

Msg 2389, Level 16, State 1, Line 15
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I don't know what that means, do you?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:37:42

does this alone work?


SELECT t.u.value('.','varchar(100)') AS album
FROM @XMLData.nodes('/collection/entry/album')t(u)

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 10:40:35

No, I get this error..

Msg 9436, Level 16, State 1, Line 2
XML parsing: line 10, character 13, end tag does not match start tag

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:51:31

your posted xml was wrong
try this


DECLARE @XMLData xml
SET @XMLData='<collection>
  <entry>
    <artist>Depeche mode</artist>
    <country></country>
    <records>
      <album rating="5">Speak and spell</album>
      <album rating="10">Greatest hits</album>
    </records>
  </entry>  
</collection>'

SELECT t.u.value('.','varchar(100)') AS album
FROM @XMLData.nodes('/collection/entry/album')t(u)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 11:03:34

[code]DECLARE @XMLData xml

SET @XMLData='<collection>
<entry>
<artist>Depeche mode</artist>
<country></country>
<records>
<album rating="5">Speak and spell</album>
<album rating="10">Greatest hits</album>
</records>
</entry>
</collection>'

SELECT u.value('.', 'varchar(100)') AS artist,
g.value('album/@rating', 'int') AS rating,
g.value('album', 'varchar(100)') AS artist
FROM @XMLData.nodes('/collection/entry/artist') as t(u)
cross apply u.nodes('records') as x(g)[/code]

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 11:06:00

Thanks, that worked.

If I wanted to use this in a stored procedure I would need to query the table and it's xml column, so I tryed this..

FROM tbl_Test.XmlData.Nodes('/collection/entry/records/album')t(u) WHERE ID = 20

But then I get this error...
Msg 317, Level 16, State 1, Line 13
Table-valued function 'Nodes' cannot have a column alias.

What am I doing wrong? And also, if I wanted to just retrive the artist and country name, how would I do then?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 11:11:58

i think it should be
FROM tbl_Test tbl
CROSS APPLY XmlData.Nodes('/collection/entry/records/album')t(u)
WHERE tbl.ID = 20

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 11:15:25

For some example of XML search capabilities, see

http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx
http://weblogs.sqlteam.com/peterl/archive/2008/03/26/Some-XML-search-approaches.aspx

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 11:17:00

Hmm...

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'FROM'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 11:26:09

quote:
Originally posted by magmo

Hmm...

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'FROM'.

is xmldata a variable or table column in your case?

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 11:28:37

XmlData is the column name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 11:30:45

can you show your full select statement?

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 11:48:09

SELECT t.u.value('.','varchar(100)') AS album
FROM tbl_test tbl
CROSS APPLY XmlData.Nodes('/collection/entry/records/album')t(u)
WHERE tbl.ID = 20

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 11:52:15

The path in the nodes (important with lower character since XML is case sensitive).

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 12:19:08

the path is correct, but when using that I get this error..

Msg 317, Level 16, State 1, Line 13
Table-valued function 'Nodes' cannot have a column alias.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:09:46

try lower case for nodes


SELECT t.u.value('.','varchar(100)') AS album
FROM tbl_test tbl
CROSS APPLY XmlData.nodes('/collection/entry/records/album')t(u) 
WHERE tbl.ID = 20

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 14:28:18

Yes, no it works fine. Thanks!

Could you also tell me how I could retrieve the artist and country element from a query, it does not have to be the same query. And one more thing, I could have another xml element that contains a album cover like a binary stream. But would that be a good idea? Or should binary streams be used only in varbinary(MAX) column?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 14:31:13

You can cross apply every level of nodes.

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-05-13 : 14:37:34

Ah, that's great. Peso, could you please advice on my question of storing binary data as a element value in a xml file

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources