Duplicate values FOR XML EXPLICIT

Question

I'm trying to create an XML output from SQL using FOR XML EXPLICIT. It has to be XML EXPLICIT because I'm using a different database, however, this example is using SQL Server.

I've declared a simple table @table for the question. It has 2 columns (letter, number) and 3 rows. The table definition:

declare @table table (letter varchar(1), number int);
insert into @table values ('A',1),('A',2),('A',3)

The query I have is this:

select 1 as tag, null as parent,
letter as [letter!1!value],
null as [number!2]
from @table

union all

select 2 as tag, 1 as parent,
letter,number
from @table
for xml explicit

The output from this is the below which I understand is happening because the values in the "letter" column are the same:

<letter value="A" />
<letter value="A" />
<letter value="A">
  <number>1</number>
  <number>2</number>
  <number>3</number>
</letter>

However, what I need the output to look like is this:

<letter value="A">
   <number>1</number>
</letter>
<letter value="A">
   <number>2</number>
</letter>
<letter value="A">
   <number>3</number>
</letter>

Is this possible and if so, how?

Answer 1

I think I found a solution. Just added another column in the table to go under TAG 1 (the parent) but with different values. Then in the ORDER BY I ordered by that column and then "number"

declare @table table (letter varchar(1), id int, number int);
insert into @table values ('A',1,1),('A',2,2),('A',3,3)


    select 1 as tag, null as parent,
    letter as [letter!1!value],
    id as [letter!1!values!hide],
    null as [number!2]
    from @table

    union all

    select 2 as tag, 1 as parent,
    letter,id,number
    from @table
    order by [letter!1!values!hide],[number!2]
    for xml explicit

Seems to do the trick :)