January 28, 2015 at 12:01 am
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.
January 28, 2015 at 12:21 am
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/61537Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply