Query XML Type Column in SQL Server 2008

Question

Hi

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"&gt;
  <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"&gt;
  <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

Answer 1

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.