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