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

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

  • Great article, thanks.

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

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

  • 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

  • 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

  • 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