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

Community · Accepted Answer · 2017-05-23 12:02:22Z

3

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

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered May 5, 2017 at 7:14

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodeReaper Over a year ago

Thanks. Definitely some best practices I will pick up from your answer

RBarryYoung · Accepted Answer · 2017-05-04 17:20:54Z

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 4, 2017 at 17:20

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

4 Comments

Gottfried Lesigang Over a year ago

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

CodeReaper Over a year ago

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.

RBarryYoung Over a year ago

@Shnugo yes, you are correct. Unfortunately, I cannot always test my answers these days because I do not have SQL Server on my iPhone.

Gottfried Lesigang Over a year ago

@RBarryYoung Answering via iPhone is a challenge :-D

Collectives™ on Stack Overflow

Working with nested XML nodes in SQL Server

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related