Query element value from xml column

Question

Post reply

Query element value from xml column

willtwc

Ten Centuries

Points: 1099
More actions
March 26, 2009 at 4:39 pm

#384227

How can I select the value of the Comment element in the example below?
<CommentHistory>
<Comment AddedOn="03/26/2009" AddedBy="testuser">This is a test</Comment>
</CommentHistory>
The value of the attributes are easily extracted using the below query.
select
ref.value('./@AddedBy', 'nvarchar(30)') as addedby,
ref.value('./@AddedOn', 'nvarchar(30)') as addedon
from
dbo.TestXML cross apply Comments.nodes('//CommentHistory/Comment')R(ref)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Could you please attach your sample XML as TXT file? 😉

Thanks

Flo

willtwc Ten Centuries Points: 1099 More actions · Answer 2

Amatuer moments!

Here you go!

</CommentHistory>

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Try this:

DECLARE @xml XML

SET @xml = '<CommentHistory>

<Comment AddedOn="03/26/2009" AddedBy="testuser">This is a test</Comment>

</CommentHistory>'

select

ref.value('./@AddedBy', 'nvarchar(30)') as addedby,

ref.value('./@AddedOn', 'nvarchar(30)') as addedon,

ref.value('./text()[1]', 'nvarchar(50)') as data

from

@xml.nodes('//CommentHistory/Comment')R(ref)

Greets

Flo

willtwc Ten Centuries Points: 1099 More actions · Answer 4

That works!

Why can't I reference the element name using this select?

select

ref.value('Comment', 'nvarchar(50)') as comments

from

dbo.TestXml cross apply Comments.nodes('//CommentHistory/')R(ref)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

merlin2864 (3/26/2009)
That works!
Why can't I reference the element name using this select?
select
ref.value('Comment', 'nvarchar(50)') as comments
from
dbo.TestXml cross apply Comments.nodes('//CommentHistory/')R(ref)

Hi

The expression "//CommentHistory/" is not a valid XPath expression 😉

You can change the expression to "//CommentHistory" and select the value with "Comment[1]" but in this case the access for the attributes is more complicated. So use the "text()" function to access the value of the current node.

Greets

Flo