Case Sensitivity in XML Extraction

I recently encountered an interesting issue where XML elements had similar names but differed in character capitalization.

Lets show it via an example:

At first, will declare an XML variable and assign the simplest XML code to it:

DECLARE @xml XML = '
<ROOT>
    <ELEMENT NAME="ElementName" VALUE="PascalCase" />
    <ELEMENT NAME="ELEMENTNAME" VALUE="UPPERCASE" />
    <ELEMENT NAME="elementname" VALUE="LOWERCASE" />
</ROOT>';

SELECT @xml;

Now will try to extract from that XML element with a name "ElementName":

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Not case sensitive'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT') AS X(C)
WHERE C.value('@NAME', 'VARCHAR(100)') = @RequestElement;

That query returned all three elements, while I expected only a single one:

That happened because SQL Server is not case sensitive by default.
In order to return the record I need I may switch the whole database I'm working in to Case Sensitive Collation or just simply add a Case Sensitive collation to my WHERE clause like this:

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Case sensitive using Collation'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT') AS X(C)
WHERE C.value('@NAME', 'VARCHAR(100)') = @RequestElement COLLATE Latin1_General_CS_AS;

That code returned me the only a single line I want:

Do you think the case is closed?

Not at all — there’s an alternative solution that may be even more elegant:

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Case sensitive via parameter'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT[@NAME=sql:variable("@RequestElement")]') AS X(C)

It also returns a single row, but without the need to worry about collation:

In this final example I've used my SQL variable "@RequestElement" as a parameter inside of an XML query, which clearly demonstrates the differentially between use of "WHERE" clause and XML node parameter:

- Filtering in the "WHERE" clause depends on SQL Server database collation, but an XML query is always case sensitive.