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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply