问题描述:
有一表tbl 如下:
col1 col2 col4
---- -------------------- ----------------------
a 1,2,3,4,5 .net,c#,delph,java,c++
b 4,6 java,J#
我要查询全部返回xml 结果集:
<tbl>
<col1>
a
</col1>
<col2>
<node id="1">.net</node>
<node id="2">c#</node>
<node id="3">delph</node>
<node id="4">java</node>
<node id="5">c++</node>
</col2>
</tbl>
<tbl>
<col1>
b
</col1>
<col2>
<node id="4">java</node>
<node id="6">J#</node>
</col2>
</tbl>
解决方案:
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: T
CREATE TABLE T(col1 VARCHAR(1),col2 VARCHAR(9),col4 VARCHAR(50))
INSERT INTO T
SELECT 'a','1,2,3,4,5','.net,c#,delph,java,c++' UNION ALL
SELECT 'b','4,6','java,J#'
GO
--SQL查询如下:
;WITH SeqNumber AS
(
SELECT TOP(50)
ROW_NUMBER() OVER(ORDER BY o.object_id) AS SeqID
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
),
Liang1 AS
(
SELECT
B.col1,
SUBSTRING(B.col2,A.SeqID,CHARINDEX(',',B.col2+',',A.SeqID)-A.SeqID) AS col2,
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY A.SeqID) AS ID
FROM SeqNumber AS A
JOIN T AS B
ON SUBSTRING(','+B.col2,A.SeqID,1)=','
),
Liang2 AS
(
SELECT
B.col1,
SUBSTRING(B.col4,A.SeqID,CHARINDEX(',',B.col4+',',A.SeqID)-A.SeqID) AS col4,
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY A.SeqID) AS ID
FROM SeqNumber AS A
JOIN T AS B
ON SUBSTRING(','+B.col4,A.SeqID,1)=','
),
Liang3 AS
(
SELECT
A.col1,
A.col2,
B.col4
FROM Liang1 AS A
JOIN Liang2 AS B
ON A.col1=B.col1
AND A.ID=B.ID
)
SELECT
col1 AS [col1],
(
SELECT
col2 AS [node/@id],
col4 AS [node/*]
FROM Liang3
WHERE col1=A.col1
FOR XML PATH(''),TYPE,ROOT('col2')
)
FROM Liang3 AS A
GROUP BY col1
FOR XML PATH('tb'),TYPE
DROP TABLE T