March 26, 2009 at 4:39 pm
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)
March 26, 2009 at 5:05 pm
Hi
Could you please attach your sample XML as TXT file? 😉
Thanks
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 26, 2009 at 5:19 pm
Amatuer moments!
Here you go!
<CommentHistory>
<Comment AddedOn="03/26/2009" AddedBy="testuser">This is a test</Comment>
</CommentHistory>
March 26, 2009 at 5:31 pm
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
How to Post Data/Code to get the best Help How to Post Performance Problems
March 26, 2009 at 6:38 pm
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)
March 27, 2009 at 3:26 am
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
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply