Multiple XML Tags in SQL

Question

I have data to select * from _temp: -

I want to create below 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>

I tried the following query: -

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

What generates: -

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

As we can see, VagueWord is somewhat, the StoryData tag is repeated for this particular UserStoryID.

I wanted the separate tag for the various UserStoryID and Vagueword tags to be repeated internally, as shown above.

How can i achieve this?

Answer 1

You can use a subquery:

;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

Conclusion:

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