sql 返回xml问题

问题描述:

 

有一表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