SQL Server XML - embedding a dataset within an existing node

Question

There are numerous examples of inserting record sets into XML, but they tend to be very basic and do not cover my specific requirement. In the following instance, I want to extract some data to XML and within that, I need a collection of nodes to represent a variable number of records. I know it can be done, as I have an example, but I do not understand how it works. I do understand my code to return the dataset might make no sense, but it is only meant to represent an example of what I am try to achieve.

SELECT 'Node' AS [A/B],
(
    SELECT x.Item
    FROM (
        SELECT 'Line1' AS [Item]
        FROM (SELECT 1 AS x) x

        UNION

        SELECT 'Line2' AS [Item]
        FROM (SELECT 1 AS x) x
    ) x
    FOR XML PATH(''), ROOT('Lines'), TYPE
)
FROM (SELECT 1 AS x) x
FOR XML PATH('Demo')

This gives me the following:

<Demo>
    <A>
        <B>Node</B>
    </A>
    <Lines>
        <Item>Line1</Item>
        <Item>Line2</Item>
    </Lines>
</Demo>

What I want is the following:

<Demo>
    <A>
        <B>Node</B>
        <Lines>
            <Item>Line1</Item>
            <Item>Line2</Item>
        </Lines>
    </A>
</Demo>

Can anyone help or point me to the correct answer please?

Answer 1

Making some assumptions on the structure of your source data that you should be able to easily adjust around, the following script gives you the output you are after, even across multiple Node groups:

Query

declare @t table([Node] varchar(10),Line varchar(10));
insert into @t values
 ('Node1','Line1')
,('Node1','Line2')
,('Node1','Line3')
,('Node1','Line4')
,('Node2','Line1')
,('Node2','Line2')
,('Node2','Line3')
,('Node2','Line4')
;

with n as
(
    select distinct [Node]
    from @t
)
select n.[Node] as [B]
      ,(select t.Line as Item
        from @t as t
        where t.[Node] = n.[Node]
        for xml path(''), root('Lines'), type
       )
from n
for xml path('A'), root('Demo')
;

---

Output

<Demo>
  <A>
    <B>Node1</B>
    <Lines>
      <Item>Line1</Item>
      <Item>Line2</Item>
      <Item>Line3</Item>
      <Item>Line4</Item>
    </Lines>
  </A>
  <A>
    <B>Node2</B>
    <Lines>
      <Item>Line1</Item>
      <Item>Line2</Item>
      <Item>Line3</Item>
      <Item>Line4</Item>
    </Lines>
  </A>
</Demo>