3

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?

  • 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
4

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

/*[lower-case(local-name(.)) = 'z']/*[lower-case(local-name(.)) = 'a']
  • 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
3

Somewhat simpler than the FLWOR version.

select *
from myTable
where yourXML.exist('(Z/A,z/a)[(b,B)=1]') = 1
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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