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

  • Rob Sheldon

    SSC Veteran

    Points: 248

    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

    Great article, thanks.

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

  • james.ingram

    SSC Journeyman

    Points: 85

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

  • timwam

    SSC-Addicted

    Points: 477

    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

    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: 11955

    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