Stairway to XML: Level 5 - The XML exist() and nodes() Methods

• Rob Sheldon

  Old Hand

  Points: 318

  More actions

  October 13, 2012 at 11:49 am

  #268541

  Comments posted to this topic are about the item Stairway to XML: Level 5 - The XML exist() and nodes() Methods

• BarneyL

  Ten Centuries

  Points: 1044

  More actions

  October 15, 2012 at 1:44 am

  #1549008

  Great article, thanks.

  It appears there's a small error though, Table 1 is missing for me.

• james.ingram

  SSC Journeyman

  Points: 85

  More actions

  October 17, 2012 at 8:37 am

  #1550055

  Both Table 1 and Table 2 appear to be missing for me.

• timwam

  SSC-Addicted

  Points: 479

  More actions

  January 30, 2013 at 6:33 am

  #1582585

  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

• dpatfield

  Grasshopper

  Points: 17

  More actions

  April 30, 2013 at 9:18 am

  #1611291

  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

• Bill Talada

  SSChampion

  Points: 11954

  More actions

  May 5, 2015 at 10:10 am

  #1795679

  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)

  ;