4

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?

1
  • 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, 2013 at 14:40

3 Answers 3

5

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

/*[lower-case(local-name(.)) = 'z']/*[lower-case(local-name(.)) = 'a']
4
  • 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 Dec 18, 2013 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 Dec 18, 2013 at 14:27
  • It seems there is a <B/> in your data? Glad you found a solution!
    – Jens Erat
    Dec 18, 2013 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.? Dec 24, 2013 at 6:03
4

Somewhat simpler than the FLWOR version.

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

Necromancing.

If you land here in search of a solution for a case-insensitive XML nodes function, this is how it goes with XPath:

DECLARE @Parameters nvarchar(max)
SET @Parameters = N'<root>
    <language>de</language>
    <LaNgUaGe>FR</LaNgUaGe>
    <LANGUAGE>iT</LANGUAGE>
    <something>else</something>
</root>'

-- XML Parameter 
DECLARE @tParameters AS xml; 
SET @tParameters = CONVERT(xml, @Parameters)
-- SELECT @tParameters 

SELECT P.Tag.value('.', 'nvarchar(MAX)') 
FROM @tParameters.nodes('//*[lower-case(local-name(.)) = "language"]') AS P(Tag) 
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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