Shredding Complex XML Structure?

• William Cokeley

  SSC Rookie

  Points: 40

  More actions

  January 28, 2015 at 12:01 am

  #302660

  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.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 28, 2015 at 12:21 am

  #1773579

  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

• William Cokeley

  SSC Rookie

  Points: 40

  More actions

  January 28, 2015 at 12:26 am

  #1773581

  @mark-3

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