XQuery/Transact SQL – Case Sensitivity

Background

When issuing XPath queries, XQuery in SQL, one has to keep in mind that though the database itself could be defined as case-insensitive, XML is case-sensitive.

 

Create & Populate Data

Create Table

SQL


declare @tbl TABLE
(
	[id] smallint not null
		identity(1,1)

	, [xml] xml
)

Populate Table with data

SQL


insert into @tbl
(
	[xml]
)
select ''

union all

select ''

union all

select ''

union all

select ''

union all

select ''

union all

select ''

Data

 

Queries

  1. Normal Queries
    • Fetch all data
    • Fetch data whose part name is Teeth
  2. Function – fn:lower-case
    • Fetch all data
    • Fetch data whose part name is Teeth ( Case In-Sensitive )

Normal Query

Fetch all Records

Code


select
          [src] = 'all'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteeth]
            = [xml].value
                (
                    '(/body/part[@name="teeth"]/@count)[1]'
                    , 'nvarchar(max)'
                ) 

from   @tbl

Output

Body Part = teeth


select
          [src] = 'name=teeth'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countofteeth]
            = [xml].value
            (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
            ) 

from   @tbl

where  [xml].exist
        (
            '(/body/part[@name="teeth"])'
        )
        = 1		 

 

Output

 

Function – Lower Case

Read All Records

SQL


select
          [src] = 'all'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteeth]
            = [xml].value
                (
                    '(/body/part[@name="teeth"]/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteethCI]
            = [xml].value
              (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
              )

from   @tbl

Output

Body Part = teeth

SQL


select 

          [src] = 'name=teeth (lowercase)'

        , *

        , [countofteeth]
            = [xml].value
            (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
            ) 

from   @tbl

where  [xml].exist
        (
            '(/body/part[fn:lower-case(@name)="teeth"])'
        )
        = 1

Output

SQL Server Version

XML datatype and XQuery was introduced in SQL Server v2005.

But, v2005, programmability support was limited.

Some String functions such as lower-case and upper-case were not introduced till v2008.

 

Summary

You know your data.

If you suspect it might be mixed-case, please take “precausive” measures.

 

References

  1. Stackoveflow
    • Kevin Aenmey
      • how to perform a case-insensitive attribute selector in xquery
        Link
  2. Microsoft
    • XQuery
      • XQuery Functions against the xml Data Type
        Link
      • Functions on String Values – lower-case
        Link
  3. IBM
    • IBM Knowledge Center
      • Home > IBM i 7.1 > Database Programming > SQL XML programming > Overview of XPath > Descriptions of XPath functions
        • fn:lower-case function
          Link
  4. W3.Org
    • Xpath Functions
      • XQuery, XPath, and XSLT Functions and Operators Namespace Document
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s