October 13, 2012 at 11:49 am
Comments posted to this topic are about the item Stairway to XML: Level 5 - The XML exist() and nodes() Methods
October 15, 2012 at 1:44 am
Great article, thanks.
It appears there's a small error though, Table 1 is missing for me.
October 17, 2012 at 8:37 am
Both Table 1 and Table 2 appear to be missing for me.
January 30, 2013 at 6:33 am
A useful article, but I think you leap to the CROSS APPLY example with nodes a little early. I only understood this having first seen an example elsewhere that used .nodes against an xml @variable - and seeing that this in itself returns a table as its result set
April 30, 2013 at 9:18 am
Perfect example for .nodes. Can we take it one step further?
Instead of concatenating First Name & Last Name, can we produce the same rowset with two columns?
First Name
Last Name
So the results might read:
ID FirstName LastName
1 John Doe
1 Jack Smith
2 Bob Jones
2 Henrietta Jones
May 5, 2015 at 10:10 am
If you want separate columns for first name and last name:
SELECT
ci.ClientID,
Person.query('.') AS Person,
Person.value('FirstName[1]','varchar(20)') AS First_Name,
Person.value('LastName[1]','varchar(20)') AS Last_Name
FROM
ClientInfo ci
CROSS APPLY
Info_untyped.nodes('/People/Person') AS People(Person)
;
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy