Create Xml with Same tag name

Question

Table Name : sample

Column Name : id,name

Every Row Create Separate tag with inside.

Show the Xml value like this

<Details>
     <id>1</id>
     <name>na</name>
     <Details>
       <id>2</id>
       <name>aa</name>
     </Details>
   </Details>

I tried like this but its not working

select 
     id       'Details\id'
     ,name    'Details\name'
from sample

How do get that xml output?

Answer 1

It is hardcoded but should work:

DECLARE @x xml

SELECT @x = (
    SELECT x+''
    FROM (
        SELECT '%details?%id?'+CAST(id as nvarchar(max))+'%/id?%name?'+name+'%/name?' x
        FROM [sample] s
        UNION ALL
        SELECT  '%/details?'
        FROM [sample] s
        ) as t
    FOR XML PATH('')
)
SELECT CAST(REPLACE(REPLACE((CAST(@x as nvarchar(max))),'%','<'),'?','>') as xml)

In [sample] table I got:

(1,'na'),
(2,'aa'),
(3,'sd')

Output:

<details>
  <id>1</id>
  <name>na</name>
  <details>
    <id>2</id>
    <name>aa</name>
    <details>
      <id>3</id>
      <name>sd</name>
    </details>
  </details>
</details>

EDIT

Also it could be done with recursive CTE:

DECLARE @x xml

;WITH rec AS (
SELECT  CAST((
            SELECT TOP 1    id,
                            [name]
            FROM [sample]
            ORDER BY id DESC
            FOR XML PATH('details')
        ) as xml) as d,
        1 as [Level]
UNION ALL
SELECT  CAST((
            SELECT  id,
                    [name],
                    cast(r.d as xml)
            FROM [sample]
            WHERE s.id = id
            FOR XML PATH('details')
        ) as xml) as d,
        r.[Level]+1
FROM [sample] s
INNER JOIN rec r
    ON s.id = CAST(r.d.query('/details/id/text()') as nvarchar(max))-1
)

SELECT TOP 1 WITH TIES d
FROM rec
ORDER BY [Level] desc

Same output.