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

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.

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