Sql-server – XML queries with case insensitive tag- which is the best query

sql serversql-server-2008-r2xmlxquery

I had asked question about XML queries with case insensitive tag earlier and i found a solution too. But i also found some other solution.
So table was something like

DECLARE @myTable TABLE ( yourXML XML )  

INSERT INTO @myTable SELECT '<z><a><b>1</b><c>2</c></a></z>'
INSERT INTO @myTable SELECT '<Z><A><b>1</b><c>2</c></A></Z>'

And all the below solutions return what i wanted(case insensitive tag)

-----Solution 1----------

SELECT * FROM @myTable WHERE ( [yourXML].exist('for $x in /*[lower-case(local-name(.)) = "z"]/*[lower-case(local-name(.)) = "a"] where  ( ($x/*[lower-case(local-name(.)) = "b"][1]) = 1 )  return $x')>0 ) 

-------------------------
-----Solution 2----------

SELECT * FROM @myTable
WHERE
(CONVERT(XML,LOWER(CONVERT(VARCHAR(MAX),[yourXML]))).exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 3----------

SELECT * FROM @myTable WHERE 
([yourXML].exist('for $x in (/Z/A,/z/a) where  ( ($x/b[1],$x/B[1]) = 1 )  return $x') > 0 ) 
-------------------------

Now i want to know which one is better to use by considering its all X-query commands(support for value(),exist(),count(),query() etc), Performance,Efficiency etc.

Best Answer

You can easily test the performance yourself.

Create a regular table that you can test your queries on.

create table myTable ( yourXML XML )

Add a couple of rows that will give you a match.

INSERT INTO myTable SELECT '<z><a><b>1</b><c>2</c></a></z>'
INSERT INTO myTable SELECT '<Z><A><b>1</b><c>2</c></A></Z>' 
INSERT INTO myTable SELECT '<Z><A><B>1</B><c>2</c></A></Z>'

Add a whole bunch of rows that will not match in different parts of the XML.

insert into myTable 
select top(10000) '<X><A><B>1</B><c>2</c></A></X>'
from sys.all_objects as o1, sys.all_objects as o2

insert into myTable 
select top(10000) '<Z><X><B>1</B><c>2</c></X></Z>'
from sys.all_objects as o1, sys.all_objects as o2

insert into myTable 
select top(10000) '<Z><A><X>1</X><c>2</c></A></Z>'
from sys.all_objects as o1, sys.all_objects as o2

Use SET STATISTICS IO (Transact-SQL) and SET STATISTICS TIME (Transact-SQL) and execute your queries in SQL Server Management Studio.

set statistics time on
set statistics io on

-----Solution 1----------

SELECT * FROM myTable WHERE ( [yourXML].exist('for $x in /*[lower-case(local-name(.)) = "z"]/*[lower-case(local-name(.)) = "a"] where  ( ($x/*[lower-case(local-name(.)) = "b"][1]) = 1 )  return $x')>0 ) 

-------------------------
-----Solution 2----------

SELECT * FROM myTable
WHERE
(CONVERT(XML,LOWER(CONVERT(VARCHAR(MAX),[yourXML]))).exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 3----------

SELECT * FROM myTable WHERE 
([yourXML].exist('for $x in (/Z/A,/z/a) where  ( ($x/b[1],$x/B[1]) = 1 )  return $x') > 0 ) 

-------------------------
-----Solution 4----------

select *
from myTable
where yourXML.exist('(Z/A,z/a)[(b,B)=1]') = 1

Switch to the message tab and evaluate execution time and required reads.

Presumably you have better data in your database to test on. The performance characteristic will change depending on the actual structure of the XML you have to deal with in the real world.

A side note is that your queries are not equivalent.
The first is easily adapted to longer element names.
The second query changes the content of the elements, not just the element names.
The third query does not really deal with case insensitive element names, it just enumerate all possible element names that in this case happens to be different in cases only. If you wanted to deal with lets say a 3 letter element name with your third solution you would have 8 (I think) different permutations to deal with.

I added a fourth solution, mostly because it is short and pretty, that has the same limitations as your third solution. It is in my measurements slightly faster than using FLWOR.

Related Question