Shredding Complex XML Structure?

  • I have an xml file that I need to read out into columns and I'm having a problem with the section that has the persons address in it, a snippit of the file is shown below:-

    DECLARE @xml XML

    SET @xml = '

    <PersonStructure>

    <PersonName>

    <GivenName>Mike</GivenName>

    <FamilyName>Myers</FamilyName>

    </PersonName>

    <PersonAddress>

    <Address>11 TON ROAD</Address>

    <Address>SWANAGE</Address>

    <Address>LONDON</Address>

    </PersonAddress>

    <Postcode>NW5 3FF</Postcode>

    <BirthDate>1968-11-11</BirthDate>

    </PersonStructure> '

    --The code am using to read out the contents is:-

    SELECT

    c0.c1.value('(GivenName/text())[1]','VARCHAR(50)') AS PGivenName,

    c0.c1.value('(FamilyName/text())[1]','VARCHAR(50)') AS FamilyName,

    c2.c3.value('(Address/text())[1]','VARCHAR(50)') AS Address1,

    c2.c3.value('(Address/text())[1]','VARCHAR(50)') AS Address2,

    c2.c3.value('(Address/text())[1]','VARCHAR(50)') AS Address3,

    c4.c5.value('(Postcode/text())[1]','VARCHAR(50)') AS Postcodes,

    c6.c7.value('(BirthDate/text())[1]','VARCHAR(50)') AS BirthDate

    FROM @xml.nodes('/PersonStructurs') AS T1(c)

    CROSS APPLY T1.c.nodes('PersonName')c0(c1)

    CROSS APPLY T1.c.nodes('PersonAddres')c2(c3)

    CROSS APPLY T1.c.nodes('Postcode')c4(c5)

    CROSS APPLY T1.c.nodes('BirthDate')c6(c7)

    Now the problem I am getting is, it is pulling out all the fields except it repeats the first part of the 'PersonAddress' field 3 times instead of giving me the whole address?

    Is there a way of gaining access to all the parts within the 'PersonAddress' node even though all 3 parts within it are named the same (Address)?

    Thanks.

  • Change

    c2.c3.value('(Address/text())[1]','VARCHAR(50)') AS Address2,

    c2.c3.value('(Address/text())[1]','VARCHAR(50)') AS Address3,

    to

    c2.c3.value('(Address/text())[2]','VARCHAR(50)') AS Address2,

    c2.c3.value('(Address/text())[3]','VARCHAR(50)') AS Address3,

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • @mark-3

    Great, works a treat, many thanks as this was my first attempt at shredding a complex XML struxture.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply