sql:xml读取解决思路_Sql Server

sql:xml读取

本帖最后由 lfz860110 于 2013-05-24 14:09:14 编辑

表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语句

SQL XML 行业数据

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


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