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


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

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&