Case insensitive XPath query?

[laredotornado]

Hi,

I'm using MS SQL Server 2005. I have this clause in a query that
looks at an XML column ...

(aaa.category_xml.exist('/categories/info_type/category/subcategory/
@name[fn:contains(.,'Mountain Biking')]') = 1)

Does anyone know a way to make the "fn:contains" clause do a case
insensitive search? Right now the search is based on case but I would
like to write one in which case doesn't matter.

Thanks for your feedback, - Dave

[Erland Sommarskog]

I looked in Books Online (for SQL 2008, which has a better XQuery
reference), and it says:

In order to get case-insensitive comparisons, the upper-case or lower-
case functions can be used.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[laredotornado]

On Aug 23, 1:34 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:

I see the fn:lower-case and fn:upper-case functions, but how do I
write the "@name[fn:contains ..." section as a fn:upper-case/lower-
case ? I am not guaranteed that this part of the XML will always be
upper or lower case.

Thanks, - Dave

[John Bell]

But you can force it to be!

There is an example at
http://msdn.microsoft.com/en-us/library/cc645590.aspx

John

[laredotornado]

Thanks, but I tried the suggestion in the link and I'm getting some
errors (keep in mind I'm using SQL Server 2005). What I have is

OR (sae.category_xml.exist('/
categories/info_type/category/@name[contains(upper-
case(.),sql:variable("@keyword"))]') = 1)

and the error I'm getting is

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: XQuery
[dbo.lasvegas.info.shows.category_xml.exist()]: There is no function
'{http://www.w3.org/2004/07/xpath-functions}:upper-case()'

Thanks for any additional help, - Dave

[Erland Sommarskog]

laredotornado (laredo...@zipmail.com) writes:
> Thanks, but I tried the suggestion in the link and I'm getting some
> errors (keep in mind I'm using SQL Server 2005). What I have is

Sorry, I did not realise that these functions were added in SQL 2008.

I guess your only option is extract the data in relational format into a
table, and then search from there. Or upgrade...