Case Sensitivity in XML Extraction (Part II)

    In my previous post I showed an elegant way to extract case sensitive data from an XML, but what if we'd like to use that method to extract all case sensitive data?

Here is how we can do it. 

At first here is our test setup:

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

SELECT @xml;

Here is our query, which would extract ONLY a single value:

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 returns a single row for only exact match for "ElementName" value:

Now I will change that query to disregard case sensitivity:

DECLARE @RequestElement SYSNAME = 'ElementName';
DECLARE @RequestElement_ci SYSNAME = UPPER(@RequestElement);

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

In order to extract all values, disregarding of case sensitivity or collation we just switched both values to an Upper case.