Creating XML Tree from SQL

Assuming MS Sql Server and starting with the table. You can not create xml tags from data with a FOR XML query. You can build xml-like strings with string aggregation, for example

select * into tbl
from (
values
 ('A' , 'B' , 'C', 'D')
,('A' , 'B' , 'C',null)
,('A' , 'B' , 'E', 'F')
,('A' , 'B' , 'E', 'G')
)t(l1,l2,l3,l4);

with t4 as(
  select l1,l2,l3, coalesce(string_agg('<' + l4 + '></' + l4 + '>',''),'') x
  from tbl 
  group by l1,l2,l3
), t3 as (
  select l1,l2, coalesce(string_agg('<' + l3 + '>' + x + '</' + l3 + '>',''),'') x
  from  t4 
  group by l1,l2
), t2 as (
  select l1, coalesce(string_agg('<' + l2 + '>' + x + '</' + l2 + '>',''),'') x
  from t3
  group by l1
)
select cast(string_agg('<' + l1 + '>' + x + '</' + l1 + '>','') as xml) res
from t2

Returns

 <A><B><C><D /></C><E><F /><G /></E></B></A>

Using powershell you can query the database and put results into a database (see : Use SqlDataAdapter and DataTable to get and update on SQL server with PowerShell). Then use the following PS script

using assembly System.Data
using assembly System.Xml.Linq

$filename = 'c:\temp\test.xml'
$dt = [System.Data.DataTable]::new()

$dt.Columns.Add('path') | out-null
$dt.Columns.Add('Level 1') | out-null
$dt.Columns.Add('Level 2') | out-null
$dt.Columns.Add('Level 3') | out-null
$dt.Columns.Add('Level 4') | out-null
$dt.Columns.Add('LEAF') | out-null


$dt.Rows.Add(('A-B-C', '---A----', '---B---', '---C---', '--------', 'N')) | out-null
$dt.Rows.Add(('A-B-C', '---A----', '---B---', '---C---', '----D---', 'N')) | out-null
$dt.Rows.Add(('A-B-C', '---A----', '---B---', '---E---', '----F---', 'N')) | out-null
$dt.Rows.Add(('A-B-C', '---A----', '---B---', '---E---', '----G---', 'N')) | out-null

function Add-Nodes
{
   param([System.Data.Datarow[]]$dt, [int]$index, [System.Xml.Linq.XElement]$node)
   $colData = $dt | Group-Object $('Level ' + $index)
   foreach($key in $colData)
   {
      $tagName = $key.Name.Trim('-')
      if($tagName -ne '')
      {
         $newNode = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get($tagName))
         $node.Add($newNode)
         if($index -lt $dt.Coumns.Count)
         {
            $childTable = $key.Group | Select-Object $_
            Add-Nodes -dt $childTable -index ($index + 1) -node $newNode
         }
      }
   }
}

$root = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('root'))
$index = 1
$dataRows = $dt | Select-Object $_
Add-Nodes -dt $dt -index $index -node $root

$root.Save($filename)

Here is the results

<?xml version="1.0" encoding="utf-8"?>
<root>
  <A>
    <B>
      <C>
        <D />
      </C>
      <E>
        <F />
        <G />
      </E>
    </B>
  </A>
</root>