logo标签列表

SQL Server 2008查询中的XML数据类型

sqlxmlsql-server-2008
6

我在SQL Server中有一张表，其中一个列是XML类型。表中还有其他不是XML的列。以下是存储在该列中的XML示例：

<AdultAsthma>
  <Group>
    <Question text="Act Score:" ForeColor="Green" />
    <Controls>
      <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black" />
      <Control type="TextBox" id="txtActScore" Answer="" />
    </Controls>
  </Group>
</AdultAsthma>

我需要的是一种查询在表中与其他列匹配的某些值，对于那些匹配的列，我想从问题节点获取文本属性和从控制节点获取答案属性。有人能帮我吗？
编辑：如果我有多个 Group 节点，需要更改什么？在这种情况下，我希望每个问题的文本和答案随着每个问题一起显示。请参见以下内容：
<AdultAsthma>
  <Group>
    <Question text="Act Score:" ForeColor="Green" />
    <Controls>
      <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black" />
      <Control type="TextBox" id="txtActScore" Answer="" />
    </Controls>
  </Group>
  <Group>
    <Question text="Do You Have Asthma?:" ForeColor="Black" />
    <Controls>
      <Control type="RadioButton" id="rbHaveAsthmaYes" text="Yes" GroupName="Diagnosed" ForeColor="Black" Answer="False" />
      <Control type="RadioButton" id="rbHaveAsthmaNo" text="No" GroupName="Diagnosed" ForeColor="Black" Answer="False" />
    </Controls>
  </Group>
</AdultAsthma>
2
请仔细查看以下内容：http://msdn.microsoft.com/zh-cn/library/ms178030.aspx - RThomas
1个回答
6
declare @T table
(
  XMLCol xml
)

insert into @T values
('<AdultAsthma>
  <Group>
    <Question text="Act Score:" ForeColor="Green" />
    <Controls>
      <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black"/>
      <Control type="TextBox" id="txtActScore" Answer="Answer" />
    </Controls>
  </Group>
</AdultAsthma>
')

select XMLCol.value(N'(/AdultAsthma/Group/Question/@text)[1]', 'nvarchar(max)'),
       XMLCol.value(N'(/AdultAsthma/Group/Controls/Control/@Answer)[1]', 'nvarchar(max)')
from @T

更新：

当您需要将XML分解为多个行时，可以在CROSS APPLY中使用.nodes()。

declare @T table
(
  XMLCol xml
)

insert into @T values
('<AdultAsthma>
  <Group>
    <Question text="Act Score:" ForeColor="Green" />
    <Controls>
      <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black" />
      <Control type="TextBox" id="txtActScore" Answer="" />
    </Controls>
  </Group>
  <Group>
    <Question text="Do You Have Asthma?:" ForeColor="Black" />
    <Controls>
      <Control type="RadioButton" id="rbHaveAsthmaYes" text="Yes" GroupName="Diagnosed" ForeColor="Black" Answer="False" />
      <Control type="RadioButton" id="rbHaveAsthmaNo" text="No" GroupName="Diagnosed" ForeColor="Black" Answer="False" />
    </Controls>
  </Group>
</AdultAsthma>
')

select X.N.value(N'(Question/@text)[1]', 'nvarchar(max)'),
       X.N.value(N'(Controls/Control/@Answer)[1]', 'nvarchar(max)')
from @T as T
  cross apply T.XMLCol.nodes(N'/AdultAsthma/Group') as X(N)
这个很好用！如果在AdultAsthma节点下有多个群组节点，我应该改变什么？请看我的编辑。 - Eric R.
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接