Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a requirement where i want XML tags to be case insensitive in my xml queries.

For example:

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>'   

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

Returns output as

yourXML
<z><a><b>1</b><c>2</c></a></z>

But i want the output to display without considering case of the XML tag. So my desired output should be

yourXML
<z><a><b>1</b><c>2</c></a></z>
<Z><A><b>1</b><c>2</c></A></Z>

How it can be done?

share|improve this question
4  
The real issue is that in XML, <Z/> and <z/> are entirely different tags. You really need to deal with your root cause which is a DATA issue, not a problem with how SQL Server is working. –  JNK Dec 18 '13 at 14:40

2 Answers 2

up vote 4 down vote accepted

Use predicates with name tests like this instead of "simple" node steps:

/*[lower-case(local-name(.)) = 'z']/*[lower-case(local-name(.)) = 'a']
share|improve this answer
    
SELECT * FROM @myTable WHERE ( [yourXML].exist('for $x in /*[lower-case(local-name(.)) = 'z']/*[lower-case(local-name(.)) = 'a'] where ( ($x/b[1]) = 1 ) return $x')>0 ) does not work and gives error –  IT researcher Dec 18 '13 at 13:08
    
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 ) worked for me –  IT researcher Dec 18 '13 at 14:27
    
It seems there is a <B/> in your data? Glad you found a solution! –  Jens Erat Dec 18 '13 at 15:25
    
Is there any option to change xml by which xml tags become no more case-sensitive and i can use query without modifying it.? –  IT researcher Dec 24 '13 at 6:03

Somewhat simpler than the FLWOR version.

select *
from myTable
where yourXML.exist('(Z/A,z/a)[(b,B)=1]') = 1
share|improve this answer
    
Is there any option to change xml by which xml tags become no more case-sensitive and i can use query without modifying it.? –  IT researcher Dec 24 '13 at 6:04
    
@ITresearcher There is no way to turn off case sensitivity in XML in SQL Server that I know of. –  Mikael Eriksson Dec 24 '13 at 7:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.