sql - SQL 中的多个 XML 标记

Question

我有以下数据供 select * from _temp :-

我想生成下面的 xml :-

<xml>
  <StoryData>
    <UserStoryId>141204</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <UserStoryID>141204</UserStoryID>
    <VagueWord>and</VagueWord>
    <VagueWord>applicable</VagueWord>
  </StoryData>

  <StoryData>
    <UserStoryId>141205</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <UserStoryID>141205</UserStoryID>
    <VagueWord>and</VagueWord>
    <VagueWord>applicable</VagueWord>
  </StoryData>  
</xml>

我尝试了以下查询:-

select distinct t1.UserStoryId,t1.Description,t1.Summary,t1.UserStoryID,t2.VagueWord
from _temp t1 left join
(
 select UserStoryId,VagueWord from _temp
) t2
on t1.UserStoryId=t2.UserStoryId
where t1.UserStoryId in (141204,141205)

FOR XML PATH('StoryData')

,ROOT('xml'),type

正在生成:-

<xml>
  <StoryData>
    <UserStoryId>141204</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <UserStoryID>141204</UserStoryID>
    <VagueWord>and</VagueWord>
  </StoryData>
  <StoryData>
    <UserStoryId>141204</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <UserStoryID>141204</UserStoryID>
    <VagueWord>applicable</VagueWord>
  </StoryData>
  <StoryData>
    <UserStoryId>141205</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <UserStoryID>141205</UserStoryID>
    <VagueWord>and</VagueWord>
  </StoryData>
  <StoryData>
    <UserStoryId>141205</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <UserStoryID>141205</UserStoryID>
    <VagueWord>applicable</VagueWord>
  </StoryData>
</xml>

正如我们所见，VagueWord 是多个，StoryData 标签针对特定的 UserStoryID 重复。

我希望不同的 UserStoryID 和 Vagueword 标签的不同标签在内部重复，如上所示。

我怎样才能做到这一点？

Answer 1

你可以使用子查询:

;WITH cte AS (
SELECT *
FROM (VALUES
(141204,'Customer can see the applicable discount on the quote and change in premium.','Customer can see the applicable discount on the quote and change in premium.','and'),
(141204,'Customer can see the applicable discount on the quote and change in premium.','Customer can see the applicable discount on the quote and change in premium.','applicable'),
(141205,'Customer can see the applicable discount on the quote and change in premium.','Customer can see the applicable discount on the quote and change in premium.','and'),
(141205,'Customer can see the applicable discount on the quote and change in premium.','Customer can see the applicable discount on the quote and change in premium.','applicable')
) as t(UserStoryId, [Description], Summary, VagueWord)
)

SELECT  UserStoryId, 
        [Description], 
        Summary, 
        (SELECT VagueWord
        FROM cte
        WHERE UserStoryId = c.UserStoryId
        FOR XML PATH(''),TYPE)
FROM cte c
GROUP BY  UserStoryId, 
        [Description], 
        Summary
FOR XML PATH('StoryData'),ROOT('xml'),TYPE

输出:

<xml>
  <StoryData>
    <UserStoryId>141204</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <VagueWord>and</VagueWord>
    <VagueWord>applicable</VagueWord>
  </StoryData>
  <StoryData>
    <UserStoryId>141205</UserStoryId>
    <Description>Customer can see the applicable discount on the quote and change in premium.</Description>
    <Summary>Customer can see the applicable discount on the quote and change in premium.</Summary>
    <VagueWord>and</VagueWord>
    <VagueWord>applicable</VagueWord>
  </StoryData>
</xml>