sql - 在 SQL Server 中使用嵌套的 XML 节点

Question

XML 附在下面。我正在使用的查询返回交叉应用于每个邮政编码的地址行。

不正确的输出:

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

如何获得下面的预期输出？基本上我只想要旁边相应邮政编码的地址行。

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

这就是我正在尝试的。

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

您应该避免向后导航。不需要 ../根本。尝试更深入地进入树层次结构:

第一个.nodes()调用将返回所有<Person> <People> 内的节点.第二次调用 .nodes()返回 <Address>节点。最后一个返回所有<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);

你可能 read this answer找到为什么 ( (.../text())[1] ) 比简单的 ...[1] 更好...