XML Collating sequence

10 Jun 2009 2:16 PM

Chloe C

Hi

If we search an XML datatype, does it use the collation properties
from the database, e.g. Case sensitivity, Accent sensitivity?

If it doesn't use the database, is there any other way of affecting
it?

Chloe

12 Jun 2009 7:08 PM

Bob Beauchemin

If you use XML in SQL Server 2005 and above, it uses the encoding specified
in the XML document itself to determine how to read the XML into in the
database. The default encoding is UTF-16 and regardless of the encoding that
you specify in the original, SQL Server converts it to UTF-16 when it's
stored. UTF-16 is the only supported output format. The XML data type in SQL
Server does not support collation. Reference
msdn.microsoft.com/en-US/library/ms345117.aspx.

Cheers,
Bob Beauchemin
SQLskills

Show quoteHide quote

"Chloe C" <ch***@mcrowdd.plus.com> wrote in message
news:qafv25prfu5su0m38j7hnuadlia5hha2vj@4ax.com...
> Hi
>
> If we search an XML datatype, does it use the collation properties
> from the database, e.g. Case sensitivity, Accent sensitivity?
>
> If it doesn't use the database, is there any other way of affecting
> it?
>
> Chloe

13 Jun 2009 2:44 AM

Michael Coles

Just to add to what Bob said, XML uses binary comparisons for comparison (in
XQuery) and sorting (the order by clause in FLWOR expressions). Efficient,
but does not account for the little things we've grown accustomed to like
dictionary sorting, accent insensitivity, case insensitivity, etc.

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X

Show quoteHide quote

"Chloe C" <ch***@mcrowdd.plus.com> wrote in message
news:qafv25prfu5su0m38j7hnuadlia5hha2vj@4ax.com...
> Hi
>
> If we search an XML datatype, does it use the collation properties
> from the database, e.g. Case sensitivity, Accent sensitivity?
>
> If it doesn't use the database, is there any other way of affecting
> it?
>
> Chloe

14 Jun 2009 9:26 PM

Bob

If you break the XML out into relational data or into a table then you can
use collations, eg

DECLARE @xml XML

SET @xml = '<test case="lower">a</test>
<test case="upper">A</test>'

SELECT *
FROM
    (
    SELECT
        x.y.value('@case','VARCHAR(10)') AS xcase,
        x.y.value('.','VARCHAR(10)') AS xvalue
    FROM @xml.nodes('//*') x(y)
    ) x
WHERE xvalue = 'A'

SELECT *
FROM
    (
    SELECT
        x.y.value('@case','VARCHAR(10)') AS xcase,
        x.y.value('.','VARCHAR(10)') AS xvalue
    FROM @xml.nodes('//*') x(y)
    ) x
WHERE xvalue = 'A' COLLATE SQL_Latin1_General_CP1_CS_AS

SELECT *
FROM
    (
    SELECT
        x.y.value('@case','VARCHAR(10)') AS xcase,
        x.y.value('.','VARCHAR(10)') AS xvalue
    FROM @xml.nodes('//*') x(y)
    ) x
WHERE xvalue = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS

Show quoteHide quote

"Chloe C" wrote:

> Hi
>
> If we search an XML datatype, does it use the collation properties
> from the database, e.g. Case sensitivity, Accent sensitivity?
>
> If it doesn't use the database, is there any other way of affecting
> it?
>
> Chloe
>

15 Jun 2009 6:56 AM

Michael Coles

But keep in mind shredding can be an expensive operation...

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X

Show quoteHide quote

"Bob" <B**@discussions.microsoft.com> wrote in message
news:AA687233-8FEA-485F-9658-6BCC498F1E19@microsoft.com...
> If you break the XML out into relational data or into a table then you can
> use collations, eg
>
> DECLARE @xml XML
>
> SET @xml = '<test case="lower">a</test>
> <test case="upper">A</test>'
>
> SELECT *
> FROM
> (
> SELECT
> x.y.value('@case','VARCHAR(10)') AS xcase,
> x.y.value('.','VARCHAR(10)') AS xvalue
> FROM @xml.nodes('//*') x(y)
> ) x
> WHERE xvalue = 'A'
>
> SELECT *
> FROM
> (
> SELECT
> x.y.value('@case','VARCHAR(10)') AS xcase,
> x.y.value('.','VARCHAR(10)') AS xvalue
> FROM @xml.nodes('//*') x(y)
> ) x
> WHERE xvalue = 'A' COLLATE SQL_Latin1_General_CP1_CS_AS
>
>
> SELECT *
> FROM
> (
> SELECT
> x.y.value('@case','VARCHAR(10)') AS xcase,
> x.y.value('.','VARCHAR(10)') AS xvalue
> FROM @xml.nodes('//*') x(y)
> ) x
> WHERE xvalue = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS
>
>
> "Chloe C" wrote:
>
>> Hi
>>
>> If we search an XML datatype, does it use the collation properties
>> from the database, e.g. Case sensitivity, Accent sensitivity?
>>
>> If it doesn't use the database, is there any other way of affecting
>> it?
>>
>> Chloe
>>