Select data in xml format using FOR XML

Question

I am writing query to select some columns in xml format.

Sample

I have data like below

Create Table #Master(Id int, Name varchar(100))

Insert Into #Master
Values(1,'Item1'),(2,'Item2')


Create Table #Sub(SubId int,MasteId int, SubName varchar(100))

Insert Into #Sub
Values(1,1,'SubItem1'),(2,1,'SubItem2')

At the moment writing query as below

Select *
From #Master as Main
FOR XML AUTO, ROOT ('ItemGroup'), ELEMENTS  XSINIL;

Which pulls the xml as below

<ItemGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Main>
    <Id>1</Id>
    <Name>Item1</Name>
  </Main>
  <Main>
    <Id>2</Id>
    <Name>Item2</Name>
  </Main>
</ItemGroup>

But I want to use link the #Master and #Sub to bring sub item in separate note for each master Item so it will generate something like below

<ItemGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Main>
    <Id>1</Id>
    <Name>Item1</Name>
    <SubItems>
      <subitem>
        <subid>1</subid>
        <masterid>1</masterid>
        <subname>SubItem1</subname>
      </subitem>
      <subitem>
        <subid>2</subid>
        <masterid>1</masterid>
        <subname>SubItem2</subname>
      </subitem>
    </SubItems>
  </Main>
  <Main>
    <Id>2</Id>
    <Name>Item2</Name>
  </Main>
</ItemGroup>

Is there any way that I can achieve this?

Answer 1

SELECT
    *,
    (
        SELECT *
        FROM #Sub AS subitem
        WHERE subitem.MasteId = Main.Id
        FOR XML AUTO, ELEMENTS, TYPE 
    ) AS SubItems
From #Master as Main
FOR XML AUTO, ROOT ('ItemGroup'), ELEMENTS XSINIL;

Answer 2

Try this,

Select *, (Select *
            From #Sub as s
            WHERE s.MasteId = Main.Id
            FOR XML PATH('subitem'), TYPE ) SubItems
From #Master as Main
FOR XML AUTO, ROOT ('ItemGroup'), ELEMENTS  XSINIL;