Here I got a table as follows:
ID PID NAME
1 -1 汽车
2 -1 飞机
3 -1 火车
4 1 小汽车
5 1 大卡车
6 1 公交车
7 4 奥迪
8 4 宝马
9 4 大众
10 4 本田
11 6 大桥六线
12 6 大桥五线
13 2 海南航空
14 2 春秋航空
15 2 上海航空
16 3 动车
17 3 绿皮车
18 3 磁悬浮
The SQL script is as follows:
SELECT ID, PID, NAME,
(SELECT ID, PID, NAME,
(SELECT ID, PID, NAME
FROM TreeTest AS Level3
WHERE Level3.pid=Level2.id for XML auto, type) --level 3
FROM TreeTest as Level2
WHERE Level2.PID=Level1.ID FOR XML AUTO,type) --level 2
FROM TreeTest as Level1
WHERE Level1.PID <0 FOR XML AUTO, --level 1
ROOT('Transport'),TYPE
I got the result as follows:
In the cast above, you see, different level has different node name, if you want they to be same, you can use raw command, after the nodes contains similar meaning.
SELECT ID, PID, NAME,
(SELECT ID, PID, NAME,
(SELECT ID, PID, NAME
FROM TreeTest AS Level3
WHERE Level3.pid=Level2.id for XML RAW('Transport'), type) --level 3
FROM TreeTest as Level2
WHERE Level2.PID=Level1.ID FOR XML RAW('Transport'),type) --level 2
FROM TreeTest as Level1
WHERE Level1.PID <0 FOR XML RAW('Transport'), --level 1
ROOT('Transports'),TYPE
You can see the result as:
The weakness here is that it can't display the xml for all the levels, that is, you must know the deepest level for the data and modify the query to get what you want. It's static code.
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
