Working with nested XML nodes in SQL Server

Question

The XML is attached below. The query I am using is returning the address lines cross applied to each post code.

Incorrect output:

Code    Reaper  PC1 PC1_AL1 PC1_AL2
Code    Reaper  PC1 PC2_AL1 PC2_AL2
Code    Reaper  PC1 PC3_AL1 PC3_AL2
... 9 rows in total

How do I get the expected output below? Basically I only want address lines for the respective post code next to it.

Code    Reaper  PC1 PC1_AL1 PC1_AL2
Code    Reaper  PC2 PC2_AL1 PC2_AL2
Code    Reaper  PC3 PC3_AL1 PC3_AL2

This is what I am trying .

DECLARE @XMLDocument XML  
SET @XMLDocument = N'<People><Person>
        <PersonDetails>
          <Surname>Code</Surname>
          <Forename>Reaper</Forename>
        </PersonDetails>
        <HomeInformation>
          <Address>
            <PostCode>PC1</PostCode>
            <AddressLines>
              <AddressLine1>PC1_AL1</AddressLine1>
              <AddressLine2>PC1_AL2</AddressLine2>
            </AddressLines>
          </Address>
          <Address>
            <PostCode>PC2</PostCode>
            <AddressLines>
              <AddressLine1>PC2_AL1</AddressLine1>
              <AddressLine2>PC2_AL2</AddressLine2>
            </AddressLines>
          </Address>
          <Address>
            <PostCode>PC3</PostCode>
            <AddressLines>
              <AddressLine1>PC3_AL1</AddressLine1>
              <AddressLine2>PC3_AL2</AddressLine2>
            </AddressLines>
          </Address>
        </HomeInformation>
      </Person>
    </People>
    '
SELECT 
    [Surname],
    [GivenName],
    [PostCode],
    [AddressLine1],
    [AddressLine2]
FROM
    (SELECT      
         ISNULL(Person.PersonDetails.value('Surname[1]', 'nvarchar(max)'),'') AS [Surname],
         ISNULL(Person.PersonDetails.value('Forename[1]', 'nvarchar(max)'),'') AS [GivenName],    
         ISNULL(HomeInformation.[Address].value('PostCode[1]', 'nvarchar(max)'),'') AS [PostCode],
         ISNULL(HomeInformationAddress.AddressLines.value('AddressLine1[1]', 'nvarchar(max)'),'') AS [AddressLine1],
         ISNULL(HomeInformationAddress.AddressLines.value('AddressLine2[1]', 'nvarchar(max)'),'') AS [AddressLine2]
     FROM  
         @XMLDocument.nodes('People/Person/PersonDetails') AS Person(PersonDetails) 
     OUTER APPLY 
         PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address])
     OUTER APPLY 
         PersonDetails.nodes('../HomeInformation/Address/AddressLines') HomeInformationAddress(AddressLines)    
    ) as X

score 3 · Answer 1 · edited May 23 '17 at 12:02

You should avoid backward navigation. There's no need for ../ at all. Try to move deeper an deeper into your tree hierarchy:

The first .nodes() call will come back with all <Person> nodes within <People>. The second call to .nodes() returns with the <Address> nodes. The last one returns all <AddressLine> elements.

SELECT      
     ISNULL(prs.value('(PersonDetails/Surname/text())[1]', 'nvarchar(max)'),'') AS [Surname],
     ISNULL(prs.value('(PersonDetails/Forename/text())[1]', 'nvarchar(max)'),'') AS [GivenName],    
     ISNULL(addr.value('(PostCode/text())[1]', 'nvarchar(max)'),'') AS [PostCode],
     ISNULL(addrLn.value('(AddressLine1/text())[1]', 'nvarchar(max)'),'') AS [AddressLine1],
     ISNULL(addrLn.value('(AddressLine2/text())[1]', 'nvarchar(max)'),'') AS [AddressLine2]
 FROM  
     @XMLDocument.nodes('People/Person') AS A(prs) 
 OUTER APPLY 
     prs.nodes('HomeInformation/Address') B(addr)
 OUTER APPLY 
     addr.nodes('AddressLines') C(addrLn);

You might read this answer to find, why ((.../text())[1]) is better than than a simple ...[1]...

Thanks. Definitely some best practices I will pick up from your answer — CodeReaper, May 05 '17 at 07:59

score 1 · Accepted Answer · answered May 04 '17 at 17:20

1

These two lines from your FROM clause are cross-joining on each other:

OUTER APPLY PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address])
OUTER APPLY PersonDetails.nodes('../HomeInformation/Address/AddressLines') HomeInformationAddress(AddressLines)

You need to make the second one dependent on the first one to prevent this:

OUTER APPLY PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address])
OUTER APPLY HomeInformation.nodes('../AddressLines') HomeInformationAddress(AddressLines)

answered May 04 '17 at 17:20

RBarryYoung

55,398
14
96
137

1

Did you try this? The second `../` is wrong. Your solution does not return correctly...The initial problem is, that the first call to `.nodes()` is diving one level to deep... Your suggestion is correct in principle, but does not repair this initial problem. Better navigate down strictly forward... – Shnugo May 05 '17 at 07:20
Thanks for the answer. Although I had to do a correction in the last line `OUTER APPLY HomeInformation.Address.nodes('AddressLines') HomeInformationAddress(AddressLines)` but I got to know exactly why my code was not working.**second one dependent on the first one** is the fix. – CodeReaper May 05 '17 at 07:58
@Shnugo yes, you are correct. Unfortunately, I cannot always test my answers these days because I do not have SQL Server on my iPhone. – RBarryYoung May 06 '17 at 05:59
1

@RBarryYoung Answering via iPhone is a challenge :-D – Shnugo May 06 '17 at 14:55

Working with nested XML nodes in SQL Server

2 Answers2