CODEWITHSUNDEEP
sql-serverxml
abacusit
abacusit

Reputation: 53

How to convert a flat table to hierarchy XML in SQL Server?

I have a flat table like this:

DIV_ID  DIV_NAME MARKET_ID MARKET_NAME TRANSIT_ID TRANS_NAME 

D1     "D1 NAME"  M1        "M1 NAME"   T1       "T1 NAME"   
D1     "D1 NAME"  M1        "M1 NAME"   T2       "T2 NAME"

Want to convert it to an xml with hierarchy, an no repeating parents:

<DIVISIONS> 
<DIVISION DIV_ID="D1" DIV_NAME="D1 NAME">   
   <MARKET MARKET_ID="M1" MARKET_NAME="M1 NAME">
     <TRANSIT TRANSIT_ID="T1" TRANSIT_NAME="T1 NAME">
     <TRANSIT TRANSIT_ID="T1" TRANSIT_NAME="T1 NAME">   
    </MARKET>   
   <MARKET MARKET_ID="M2" MARKET_NAME="M2 NAME">   
   ...   
   </MARKET> 
  </DIVISION> 
<DIVISION DIV_ID="" DIV_NAME=""> 
 ... 
</DIVISION> 
</DIVISIONS>

It seems SQL can generate it directly by using "FOR XML" with some parameters. I tried a few but still can't get what I want.

Any help is appreciated. Thanks!

EDIT: I found a solution, but SQL Server output '&lt;' and '&gt;' instead of "<",">" in the nested elements. Here is my code:

SELECT DISTINCT DIV_ID AS '@DIV_ID', DIV_NAME AS '@DIV_NAME', 
  (SELECT DISTINCT MARKET_ID AS '@MARKET_ID', MARKET_NAME AS '@MARKET_NAME',
     (SELECT TRANSIT_ID AS '@TRANSIT_ID', TRANSIT_NAME AS '@TRANSIT_NAME'
     FROM HX_DIVISIONS T3
     WHERE T3.MARKET_ID = T2.MARKET_ID
     FOR XML PATH ('TRANSIT')
     ) TRANSITS
    FROM HX_DIVISIONS T2
    WHERE T2.DIV_ID = T1.DIV_ID
    FOR XML PATH ('MARKET') ) MARKETS
FROM HX_DIVISIONS T1
FOR XML PATH('DIVISION'), ROOT('DIVISIONS')

I tried to used REPLACE(XXX, '&lt;','<') as another post suggested, but it doesn't work.

Output is like this:

<DIVISIONS>
  <DIVISION DIV_ID="D1" DIV_NAME="D1 NAME">
    <MARKETS>&lt;MARKET MARKET_ID="M1" MARKET_NAME="M1 NAME"&gt;&amp;lt;TRANSITS&gt;&lt;TRANSIT TRANSIT_ID="T1" TRANSIT_NAME="T1 NAME"/&gt;
....
</DIVISIONS>

Upvotes: 0

Views: 606

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

Try it like this:

DECLARE @tbl TABLE(DIV_ID VARCHAR(10), DIV_NAME VARCHAR(10), TRANSIT_ID VARCHAR(10), TRANSIT_NAME VARCHAR(10), MARKET_ID VARCHAR(10), MARKET_NAME VARCHAR(10));
INSERT INTO @tbl VALUES
 ('D1','D1 NAME','T1','T1 NAME','M1','M1 NAME')
,('D1','D1 NAME','T1','T1 NAME','M2','M2 NAME');

WITH DistinctDIVIDs AS
(
    SELECT DISTINCT DIV_ID,DIV_NAME 
    FROM @tbl
)
SELECT DIVs.DIV_ID AS [@DIV_ID]
      ,DIVs.DIV_NAME AS [@DIV_NAME]
      ,(
          SELECT markets.MARKET_ID AS [@MARKET_ID]
                ,markets.MARKET_NAME AS [@MARKET_NAME]
                ,(
                    SELECT transits.TRANSIT_ID AS [@TRANSIT_ID]
                          ,transits.TRANSIT_NAME AS [@TRANSIT_NAME]  
                    FROM @tbl AS transits
                    WHERE transits.DIV_ID=DIVs.DIV_ID AND transits.MARKET_ID=markets.MARKET_ID 
                    FOR XML PATH('TRANSIT'),TYPE
                 )
          FROM @tbl AS markets
          WHERE markets.DIV_ID=DIVs.DIV_ID
          FOR XML PATH('MARKET'),TYPE
       ) 
FROM DistinctDIVIDs AS DIVs
FOR XML PATH('DIVISION'),ROOT('DIVISIONS')

The result

<DIVISIONS>
  <DIVISION DIV_ID="D1" DIV_NAME="D1 NAME">
    <MARKET MARKET_ID="M1" MARKET_NAME="M1 NAME">
      <TRANSIT TRANSIT_ID="T1" TRANSIT_NAME="T1 NAME" />
    </MARKET>
    <MARKET MARKET_ID="M2" MARKET_NAME="M2 NAME">
      <TRANSIT TRANSIT_ID="T1" TRANSIT_NAME="T1 NAME" />
    </MARKET>
  </DIVISION>
</DIVISIONS>

Upvotes: 3

Related Questions