Working with Nested XML Nodes in SQL Server
XML is shown below. The query I am using returns the intersection of the address strings applied to each post.
Invalid 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 the address lines for the corresponding postal 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 to do.
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
source to share
These two lines from your proposal FROM
are interconnected with each other:
OUTER APPLY PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address])
OUTER APPLY PersonDetails.nodes('../HomeInformation/Address/AddressLines') HomeInformationAddress(AddressLines)
To prevent this, you need to do the second, depending on the first:
OUTER APPLY PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address])
OUTER APPLY HomeInformation.nodes('../AddressLines') HomeInformationAddress(AddressLines)
source to share
You should avoid backward navigation. No need at ../
all. Try to dive deeper into the tree hierarchy:
The first call .nodes()
will return with all nodes <Person>
within <People>
. The second call .nodes()
returns with nodes <Address>
. The latter returns all elements <AddressLine>
.
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 can read this answer to find why ( (.../text())[1]
) is better than simple ...[1]
...
source to share