sql:xml读取解决方案

sql:xml读取
表A,字段rule是xml类型 ，questionCondition 标签中的id是另外一张表B的主键
表B如下
ID  value
Q1  10001
Q2  20001
Q3  30001
Q4  40001
表A记录为：
id  name  rule
1   ddd   下面xml
xml如下：
 <legendRule>
    <questionRule>
<questionCondition id="Q1">
    <answer>A</answer>
     <answer>B</answer>
</questionCondition >
<questionCondition id="Q2">
     <answer>C</answer>
     <answer>D</answer>
</questionCondition>
<questionCondition id="Q3">
     <answer>B</answer>
     <answer>C</answer>
</questionCondition>
<questionCondition id="Q4">
     <answer>A</answer>
     <answer>C</answer>
</questionCondition>
    </questionRule>
</legendRule>

我现在要如何得到一个字符串为 "10001:A、B; 20001:C、D; 30001: B、C; 40001:A、C"
最终我要获得数据集为
id  name   rule
1    ddd   "10001:A、B; 20001:C、D; 30001: B、C; 40001:A、C"
求SQL语句
------解决方案--------------------

create table 表A

(id int, name varchar(5), [rule] xml)


insert into 表A(id,name,[rule])

select 1, 'ddd',

'<legendRule>

<questionRule>

<questionCondition id="Q1">

   	<answer>A</answer>

    <answer>B</answer>

</questionCondition>

<questionCondition id="Q2">

    	<answer>C</answer>

    	<answer>D</answer>

</questionCondition>

<questionCondition id="Q3">

    	<answer>B</answer>

    	<answer>C</answer>

</questionCondition>

<questionCondition id="Q4">

    	<answer>A</answer>

    	<answer>C</answer>

</questionCondition>

</questionRule>

</legendRule>'



with t1 as

(select a.id,

        o.value('../@id','varchar(5)') 'ids',

        o.value('.','varchar(5)') 'ans'

 from 表A a

 cross apply [rule].nodes('/legendRule/questionRule/questionCondition/answer') x(o)

),t2 as

(select a.id,

        a.ids+':'+stuff((select '、'+b.ans from t1 b 

                         where b.id=a.id and a.ids=b.ids 

                         for xml path('')),1,1,'') 'ids2'

 from t1 a

 group by a.id,a.ids

),t3 as

(select a.id,

        stuff((select ';'+b.ids2 from t2 b where b.id=a.id for xml path('')),1,1,'') 'rule'

  from t2 a

  group by a.id

)

select a.id,

       a.name,

       b.[rule]

 from 表A a

 left join t3 b on a.id=b.id



/*

id          name  rule

----------- ----- -----------------------------------

1           ddd   Q1:A、B;Q2:C、D;Q3:B、C;Q4:A、C


(1 row(s) affected)

*/

------解决方案--------------------

 
 --> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO 
CREATE TABLE #tb([ID] VARCHAR(2),[value] INT)
INSERT #tb
SELECT 'Q1',10001 UNION ALL
SELECT 'Q2',20001 UNION ALL
SELECT 'Q3',30001 UNION ALL
SELECT 'Q4',40001

--> 测试数据：#ta
IF OBJECT_ID('TEMPDB.DBO.#ta') IS NOT NULL DROP TABLE #ta
GO 
CREATE TABLE #ta([id] VARCHAR(20),[name] VARCHAR(8),[rule] XML)
INSERT #ta
SELECT '1','ddd','<legendRule>
     <questionRule>
 <questionCondition id="Q1">
     <answer>A</answer>
      <answer>B</answer>
 </questionCondition >
 <questionCondition id="Q2">
      <answer>C</answer>
      <answer>D</answer>
 </questionCondition>
 <questionCondition id="Q3">