sql xml查询数据集
数据库A表有一个字段(xmlcontent)类型是xml，表中两条记录
xmlcontent 记录1：
<root>
<questions>
<question id="Q1">
<answer>aaa</answer>
<answer>bbb</answer>
</question>
<question id="Q2">
<answer>bbb</answer>
<answer>ddd</answer>
</question>
</questions>
</root>
xmlcontent 记录2：
<root>
<questions>
<question id="Q1">
<answer>aaa</answer>
<answer>ccc</answer>
</question>
<question id="Q2">
<answer>aaa</answer>
<answer>bbb</answer>
</question>
</questions>
</root>
我现在需要通过查询统计出，所有answer标签中不重复的值。以字符串形式查询得出"aaa,bbb,ccc,ddd"
或者以表的结果查询
id column
1 aaa
2 bbb
3 ccc
4 ddd
SQL XML 数据库
[解决办法]
create table A表(xmlcontent xml)
insert into A表
select '
<root>
<questions>
<question id="Q1">
<answer>aaa</answer>
<answer>bbb</answer>
</question>
<question id="Q2">
<answer>bbb</answer>
<answer>ddd</answer>
</question>
</questions>
</root>' union all
select '
<root>
<questions>
<question id="Q1">
<answer>aaa</answer>
<answer>ccc</answer>
</question>
<question id="Q2">
<answer>aaa</answer>
<answer>bbb</answer>
</question>
</questions>
</root>'
-- 以字符串形式查询
with t as
(select distinct o.value('.','varchar(10)') 'answer'
from A表
cross apply xmlcontent.nodes('/root/questions/question/answer') x(o)
)
select stuff((select ','+answer from t for xml path('')),1,1,'') 'answers'
/*
answers
-------------------------
aaa,bbb,ccc,ddd
(1 row(s) affected)
*/
-- 以表的结果查询
select row_number() over(order by t.answer) 'id', t.answer
from
(select distinct o.value('.','varchar(10)') 'answer'
from A表
cross apply xmlcontent.nodes('/root/questions/question/answer') x(o)
) t
/*
id answer
-------------------- ----------
1 aaa
2 bbb
3 ccc
4 ddd
(4 row(s) affected)
*/
