Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate xml using FOR XML PATH from table with hierarchy Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 6:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 5:18 PM
Points: 5, Visits: 38
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
Post #1465433
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse