Creating XML Tree from SQL

Question

I have SQL table which has following data

path	Level 1	Level 2	Level 3	Level 4	LEAF
A-B-C	---A----	---B---	---C---	--------	N
A-B-C-D	---A----	---B---	---C---	----D---	Y
A-B-E-F	---A----	---B---	---E---	----F---	Y
A-B-E-G	---A----	---B---	---E---	----G---	Y

Now based on above data I want form xml tree hierarchy and save the data as .xml file I am using SQL, SSIS and XSLT Template

Expected Output should be like

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

Could anyone help how I can achieve this?

I have tried with for xml path approach but I couldn't get it. Solution with xslt template will do

The input to XSLT is XML. If you have an XML input, add it to your question. — michael.hor257k, Apr 24 '23 at 12:50
A-B-C A B C N A-B-C-D A B C D Y A-B-E-F A B E F Y This would be xml input — Anurag K., Apr 24 '23 at 13:06
Please don't post code in comments. Edit your question instead. Also state which processor you will be using (see: https://stackoverflow.com/a/25245033/3016153). — michael.hor257k, Apr 24 '23 at 13:24
See if one these helps: https://stackoverflow.com/a/30672395/3016153, https://stackoverflow.com/a/33387703/3016153. — michael.hor257k, Apr 24 '23 at 13:26

Serg · Answer 1 · 2023-04-24T14:04:58.193

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>

score 0 · Answer 2 · answered Apr 24 '23 at 17:26

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>

Creating XML Tree from SQL

2 Answers2