XML queries with case insensitive tag

Question

Post reply

XML queries with case insensitive tag

IT researcher

SSCertifiable

Points: 7602
More actions
December 18, 2013 at 5:36 am

#302468

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>1<c>2</c></a></z>'
INSERT INTO @myTable SELECT '<Z><A>1<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>1<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>1<c>2</c></a></z>
<Z><A>1<c>2</c></A></Z>
How it can be done?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

KS Hall of Fame Points: 3323 More actions · Answer 1

Try this...

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 )

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Sowbhari's solution is likely the most straightforward, but does have the (perhaps) nasty side-effect of forcing everything to lower case (i.e. data AND element names). If you absolutely must only do that to element names, you'd have to adjust your FLWOR query.

Something to the effect of

for $x in /* where lower-case(node-name($x))="r"

for $y in $x/* where lower-case(node-name($y))="a" and ( ($y/b[1]) = 1 )

return $x

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?