How to query the xml in SQL XML on a SQL Server 2008 database

Question

I have a SQL Server 2008 database. There is a table called Documents with the following schema:

 Id            int     PK      
 DocumentXml   xml

The xml documents all look something like:

<docroot>
    <name>Some Name</name>
</docroot>

I want to select all the records where the text value of /docroot/name begins with an "S" (case-insenstive).

How do I execute this query with the best performance?

Answer 1

Not sure about the performance - but you can do something like this:

SELECT (list of columns)
FROM dbo.Documents
WHERE DocumentXml.value('(/docroot/name)[1]', 'varchar(100)') LIKE 'S%'

If your database collation is case-insensitive, this LIKE operation will be case-insensitive, too.

Answer 2

I want to select all the records where the text value of /docroot/name begins with an "S" (case-insenstive).

Use this XPath expression:

/*/name[starts-with(translate(.,'S','s'), 's')]