CODEWITHSUNDEEP
sql-serverxquery
RobSiklos
RobSiklos

Reputation: 8849

how to perform a case-insensitive attribute selector in xquery

I'm trying to query an XML column in SQL server, and I want to select records where an attribute value matches a case-insensitive string.

For instance, consider the following value for the XML column:

<items>
  <item k="Description" v="hello" />
</items>

Right now, my select looks like this:

SELECT
MyXmlColumn.value('(/items/item[@k="Description"]/@v)[1]', 'nvarchar(max)') as v
FROM Table

The problem is that the value of the "k" attribute could be 'Description', 'description', or 'DESCRIPTION'.

How do I write the XQuery so that it performs a case-insensitive match?

Upvotes: 5

Views: 6096

Answers (1)

Kevin Aenmey
Kevin Aenmey

Reputation: 13419

You can use the lower-case function fn:lower-case (which will make the attributes you are searching on lower-case) and then make sure that in your query you use lower-case:

SELECT 
MyXmlColumn.value('(/items/item[fn:lower-case(@k)="description"]/@v)[1]', 'nvarchar(max)') as v
FROM [Table]

Upvotes: 8

Related Questions