使用 Transact-SQL 连接来自不同元素的 XML 数据

debugcn 发表于 Dev

用户3457834

我正在尝试使用 OPENXML 函数组合来自不同节点的信息。例如，使用以下 xml 数据，我想填写尽可能多的数据并根据“PartyId”加入，以便我可以从“MoreInfo”获取“PartyRoleCode”：

 <Root>
   <Parties>
      <Party PartyId="Party_1">
         <PartyTypeCode PartyTypeCode="Bastard"></PartyTypeCode>
         <PersonInfo>
            <FirstName>Jon</FirstName>
            <LastName>Snow</LastName>
            <Gender>M</Gender>
         </PersonInfo>
         <EmailAddress>[email protected]</EmailAddress>
      </Party>
      <Party PartyId="Party_2">
         <PartyTypeCode PartyTypeCode="Stark"></PartyTypeCode>
         <PersonInfo>
            <FirstName>Eddard</FirstName>
            <LastName>Stark</LastName>
         </PersonInfo>
      </Party>
   </Parties>
     <MoreInfo>
         <Parties>
            <PartyRef PartyId="Party_1">
               <PartyRole PartyRoleCode="Nights Watch"></PartyRole>
            </PartyRef>
            <PartyRef PartyId="Party_1">
               <PartyRole PartyRoleCode="Wildling"></PartyRole>
            </PartyRef>
            <PartyRef PartyId="Party_2">
               <PartyRole PartyRoleCode="Kings Hand"></PartyRole>
            </PartyRef>
         </Parties>
   </MoreInfo>
</Root>

我怎么能输出以下内容：

PartyRolecode   FirstName   LastName   Gender   Email Address
-------------   ---------   --------   ------   -------------
Nights Watch    Jon         Snow       M        [email protected]
Wildling        Jon         Snow       M        [email protected]
Kings Hand      Eddard      Stark

修吾

像这样尝试：

DECLARE @xml XML=
N'<Root>
   <Parties>
      <Party PartyId="Party_1">
         <PartyTypeCode PartyTypeCode="Bastard"></PartyTypeCode>
         <PersonInfo>
            <FirstName>Jon</FirstName>
            <LastName>Snow</LastName>
            <Gender>M</Gender>
         </PersonInfo>
         <EmailAddress>[email protected]</EmailAddress>
      </Party>
      <Party PartyId="Party_2">
         <PartyTypeCode PartyTypeCode="Stark"></PartyTypeCode>
         <PersonInfo>
            <FirstName>Eddard</FirstName>
            <LastName>Stark</LastName>
         </PersonInfo>
      </Party>
   </Parties>
     <MoreInfo>
         <Parties>
            <PartyRef PartyId="Party_1">
               <PartyRole PartyRoleCode="Nights Watch"></PartyRole>
            </PartyRef>
            <PartyRef PartyId="Party_1">
               <PartyRole PartyRoleCode="Wildling"></PartyRole>
            </PartyRef>
            <PartyRef PartyId="Party_2">
               <PartyRole PartyRoleCode="Kings Hand"></PartyRole>
            </PartyRef>
         </Parties>
   </MoreInfo>
</Root>';

-- 查询将读取所有<Party>元素，用于APPLY读取@PartyId和使用另一个APPLY读取相关<PartyRef>元素，使用@PartyId作为结果集的列sql:column()：

SELECT B.PartyID
      ,pr.value(N'(PartyRole/@PartyRoleCode)[1]',N'nvarchar(max)') AS PartyRoleCode
      ,p.value(N'(PersonInfo/FirstName/text())[1]',N'nvarchar(max)') AS FirstName
      ,p.value(N'(PersonInfo/LastName/text())[1]',N'nvarchar(max)') AS LastName
      ,p.value(N'(PersonInfo/Gender/text())[1]',N'nvarchar(max)') AS Gender
      ,p.value(N'(EmailAddress/text())[1]',N'nvarchar(max)') AS eMail
FROM @xml.nodes(N'/Root/Parties/Party') AS A(p)
OUTER APPLY(SELECT p.value(N'@PartyId','nvarchar(max)') AS PartyID) AS B
OUTER APPLY @xml.nodes(N'Root/MoreInfo/Parties/PartyRef[@PartyId=sql:column("B.PartyID")]') AS C(pr);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。