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

Answer 1

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]...

Answer 2

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)