Using XQuery in SQL Server 2008 to search for content

Question

Take the following 4 example XML documents:

1. <Example> <Colour>orange</Colour> </Example>

2. <Example> <Car colour="orange">Ford Focus</Car> </Example>

3. <Example> <County>Orange County</County> </Example>

4. <Example> <Orange>555</Orange> </Example>

These are all stored in a SQL Server database in a table with a XML datatype column (untyped).

How would I go about running a query looking for all content in the document with the word orange in it, which would return the following documents:

1. this has a value orange inside an element.
2. this has a value orange inside an attribute.
3. this has a value Orange County inside an element (note different casing of the word Orange)

Document 4 should not be returned in the query results as the word orange is an element name and is not a data value.

Is this even possible?

Thanks in advance.

Solution

I don't think you can do it in a single query - however, with two, you should get the results you're looking for:

1. first query to get all XML nodes that contain a text (inside the element) that looks like "orange":

   SELECT * FROM dbo.XQueryTest
   WHERE XmlContent.value('(//*/text())[1]', 'varchar(50)') LIKE '%orange%'
   

2. second query to do the same, but based on an attribute value:

   SELECT * FROM dbo.XQueryTest
   WHERE XmlContent.value('(//*/@*)[1]', 'varchar(50)') LIKE '%orange%'
   

Query 1 just grabs the value of the text() for all XML nodes as a string (varchar(50)) and compares that based on regular SQL syntax against '%orange%'.

Query no. 2 grab all the attribute values (/@*) and does the same.

Of course, you can UNION those two together, if you really need to.

Hope this helps!