SQL Server 2008 中查询 XML 类型列

发布于 2024-09-18 04:37:59 字数 1598 浏览 6 评论 0原文

我在 SQL2008 数据库中创建了一个具有强类型 XML 列的表。我无法理解文档或示例，这些文档或示例显示了如何执行简单的查询来选择特定元素的值为 true 的行。

在不引发错误的情况下，我能得到的最接近结果是：

SELECT Person.*
FROM [Profile].[Person]
WHERE Flags.exist('
   declare namespace ws="http://schema/profile/person/2010/09/flags";
   ws:root/ws:CompetitionExclusion = xs:boolean("false")') = 1

但是此查询返回所有行，而不仅仅是那些我感兴趣的元素为 false 的行。

简单示例：

已定义的 XML 架构集合

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:ws="http://schema/profile/person/2010/09/flags"
           attributeFormDefault="unqualified"
           elementFormDefault="qualified"
           targetNamespace="http://schema/profile/person/2010/09/flags"
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="CompetitionExclusion" type="xs:boolean" />
        <xs:element name="EnrolmentExclusion" type="xs:boolean" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

示例 XML 存储在如果有人可以向我展示如何构造XQuery

<?xml version="1.0" encoding="utf-16"?>
<ws:root xmlns:ws="http://schema/profile/person/2010/09/flags">
  <ws:CompetitionExclusion>true</ws:CompetitionExclusion>
  <ws:EnrolmentExclusion>false</ws:EnrolmentExclusion>
</ws:root>

，该 XQuery 只返回那些 CompetitionExclusion 元素带有 false 的行，那就太好了。我发现的所有示例都倾向于处理属性而不是元素。

谢谢

原文

I have created a table that has a strongly type XML column in SQL2008 database. I'm having trouble understanding the documentation or samples that show how I can do a simple query to select the rows that have a value of true for a particular element.

The closest I can get without having errors thrown is:

SELECT Person.*
FROM [Profile].[Person]
WHERE Flags.exist('
   declare namespace ws="http://schema/profile/person/2010/09/flags";
   ws:root/ws:CompetitionExclusion = xs:boolean("false")') = 1

But this query is return all rows not just those that are false for the element I am interested in.

Simple Example:

Defined XML Schema Collection

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:ws="http://schema/profile/person/2010/09/flags"
           attributeFormDefault="unqualified"
           elementFormDefault="qualified"
           targetNamespace="http://schema/profile/person/2010/09/flags"
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="CompetitionExclusion" type="xs:boolean" />
        <xs:element name="EnrolmentExclusion" type="xs:boolean" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Sample XML stored in a row

<?xml version="1.0" encoding="utf-16"?>
<ws:root xmlns:ws="http://schema/profile/person/2010/09/flags">
  <ws:CompetitionExclusion>true</ws:CompetitionExclusion>
  <ws:EnrolmentExclusion>false</ws:EnrolmentExclusion>
</ws:root>

If someone could show me how to construct the XQuery that would return only those rows with false for CompetitionExclusion element that would be great. All the samples I have found tend to deal with attributes rather than elements.

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小红帽 2024-09-25 04:37:59

我会尝试这样的事情：

;WITH XMLNAMESPACES('http://schema/profile/person/2010/09/flags' as ws)
SELECT Person.*
FROM [Profile].[Person]
WHERE Flags.value('(ws:root/ws:CompetitionExclusion)[1]', 'varchar(20)') = 'false'

在我看来，使用 XQuery 和使用布尔标志查询 XML 总是有点混乱 - 我通常只是将这些条目视为字符串并进行字符串比较。

I would try something like this:

;WITH XMLNAMESPACES('http://schema/profile/person/2010/09/flags' as ws)
SELECT Person.*
FROM [Profile].[Person]
WHERE Flags.value('(ws:root/ws:CompetitionExclusion)[1]', 'varchar(20)') = 'false'

Querying XML using XQuery and using boolean flags is always a bit of a mess, in my opinion - I typically just treat those entries as string and do a string comparison.

回复收藏 0 原文

~没有更多了~