Generate xml using FOR XML PATH from table with hierarchy

• nkzhangtao

  SSC Enthusiast

  Points: 157

  More actions

  June 19, 2013 at 6:28 pm

  #274256

  I need to create xml from a table like:

  EL1 EL2 EL3 Attr01 Attr02 Attr03 Attr04

  E10, , ,a,b,c,d

  E10,E1010, ,a,b,c,d

  E10,E1010,E101010,a,b,c,d

  E10,E1010,E101020,a,b,c,d

  E10,E1010,E101030,a,b,c,d

  E10,E1020, ,a,b,c,d

  E10,E1020,E102010,a,b,c,d

  E20, , ,a,b,c,d

  E20,E2010, ,a,b,c,d

  E20,E2010,E201010,a,b,c,d

  E20,E2020, ,a,b,c,d

  E20,E2020,E202010,a,b,c,d

  E20,E2020,E202020,a,b,c,d

  The hierarchy is EL1--EL2--EL3, and the 3 columns should be elements of xml;

  The other for columns Attr01,Attr02,Attr03,Attr04 should be attributes of xml;

  The actual table could have more than 500 rows(there are many values for El1,EL2,and EL3).

  The expected xml should like:

  <root>

  <E10 Attr01="a" Attr02="b" Attr03="c" Attr04="d">

  <E1010 Attr01="a" Attr02="b" Attr03="c" Attr04="d">

  <E101010 Attr01="a" Attr02="b" Attr03="c" Attr04="d" />

  <E101020 Attr01="a" Attr02="b" Attr03="c" Attr04="d" />

  <E101030 Attr01="a" Attr02="b" Attr03="c" Attr04="d" />

  </E1010>

  <E1020 Attr01="a" Attr02="b" Attr03="c" Attr04="d">

  <E102010 Attr01="a" Attr02="b" Attr03="c" Attr04="d" />

  </E1020>

  </E10>

  <E20 Attr01="a" Attr02="b" Attr03="c" Attr04="d">

  <E2010 Attr01="a" Attr02="b" Attr03="c" Attr04="d">

  <E201010 Attr01="a" Attr02="b" Attr03="c" Attr04="d" />

  </E2010>

  <E2020 Attr01="a" Attr02="b" Attr03="c" Attr04="d">

  <E202010 Attr01="a" Attr02="b" Attr03="c" Attr04="d" />

  <E202020 Attr01="a" Attr02="b" Attr03="c" Attr04="d" />

  </E2020>

  </E20>

  </root>

  I create a sample Src table:

  CREATE TABLE Src

  (

  EL1 VARCHAR(10),

  EL2 VARCHAR(10),

  EL3 VARCHAR(10),

  Attr01 VARCHAR(10),

  Attr02 VARCHAR(10),

  Attr03 VARCHAR(10),

  Attr04 VARCHAR(10)

  )

  GO

  INSERT INTO Src

  (EL1,EL2,EL3,Attr01,Attr02,Attr03,Attr04

  )

  SELECT 'E10','','','a','b','c','d'

  UNION SELECT 'E10','E1010','','a','b','c','d'

  UNION SELECT 'E10','E1010','E101010','a','b','c','d'

  UNION SELECT 'E10','E1010','E101020','a','b','c','d'

  UNION SELECT 'E10','E1010','E101030','a','b','c','d'

  UNION SELECT 'E10','E1020','','a','b','c','d'

  UNION SELECT 'E10','E1020','E102010','a','b','c','d'

  UNION SELECT 'E20','','','a','b','c','d'

  UNION SELECT 'E20','E2010','','a','b','c','d'

  UNION SELECT 'E20','E2010','E201010','a','b','c','d'

  UNION SELECT 'E20','E2020','','a','b','c','d'

  UNION SELECT 'E20','E2020','E202010','a','b','c','d'

  UNION SELECT 'E20','E2020','E202020','a','b','c','d'

  GO

  I tried to use FOR XML PATH to generate xml for the sample data. When the records increase to a few hundreds, it's not a good idea.

  Here is my script:

  SELECT

  (SELECT Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  FROM Src

  WHERE EL3 = 'E101010'

  FOR XML PATH('E101010'),TYPE

  ) AS 'node()'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  FROM Src

  WHERE EL3 = 'E101020'

  FOR XML PATH('E101020'),TYPE

  ) AS 'node()'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  FROM Src

  WHERE EL3 = 'E101030'

  FOR XML PATH('E101030'),TYPE

  ) AS 'node()'

  FROM Src

  WHERE EL2 = 'E1010' AND (EL1 <>'' AND EL3 ='')

  FOR XML PATH('E1010'),TYPE

  ) AS 'node()'--1010

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  FROM Src

  WHERE EL3 = 'E102010'

  FOR XML PATH('E102010'),TYPE

  ) AS 'node()'

  FROM Src

  WHERE EL2 = 'E1020' AND (EL1 <>'' AND EL3 ='')

  FOR XML PATH('E1020'),TYPE

  ) AS 'node()'--1020

  FROM Src

  WHERE EL1 = 'E10' AND (EL2 ='' AND EL3 ='')

  FOR XML PATH('E10'),TYPE) 'node()'

  ,(SELECT Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  FROM Src

  WHERE EL3 = 'E201010'

  FOR XML PATH('E201010'),TYPE

  ) AS 'node()'

  FROM Src

  WHERE EL2 = 'E2010' AND (EL1 <>'' AND EL3 ='')

  FOR XML PATH('E2010'),TYPE

  ) AS 'node()'--2010

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  FROM Src

  WHERE EL3 = 'E202010'

  FOR XML PATH('E202010'),TYPE

  ) AS 'node()'

  ,( SELECT

  Attr01 AS '@Attr01'

  ,Attr02 AS '@Attr02'

  ,Attr03 AS '@Attr03'

  ,Attr04 AS '@Attr04'

  FROM Src

  WHERE EL3 = 'E202020'

  FOR XML PATH('E202020'),TYPE

  ) AS 'node()'

  FROM Src

  WHERE EL2 = 'E2020' AND (EL1 <>'' AND EL3 ='')

  FOR XML PATH('E2020'),TYPE

  )

  FROM Src

  WHERE EL1 = 'E20' AND (EL2 ='' AND EL3 ='')

  FOR XML PATH('E20'),TYPE) AS 'node()'

  FOR XML PATH(''),ROOT('root')

  If I get a few hundreds of rows, how huge the script should be. Does anyone have better solution for this?

  Thanks,

  Tao