Using XQuery in SQL Server 2008 to Search for Content

Question

Take the following 4 examples of XML documents:

• <Example> <Color> orange </ color> </ Example>

• <Example> <cars Color = "orange"> Ford Focus </ & GT cars; </ Example>

• <Example> <& County GT, Orange County </ county> </ Example>

• <Example> <& Orange GT; 555 </ Orange> </ Example>

They are all stored in a SQL Server database in a table with an XML data type column (untyped).

How can I run a query that searches for all content in a document with the word "orange" in it, which will return the following documents:

• this value is marked inside the element.
• this value is marked inside the attribute.
• it has an Orange County value inside the element (note the different shell of the word Orange)

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

Is it possible?

Thanks in advance.

Answer 1

I don't think you can do this in one query - however, with two, you should get the results you are looking for:

• first request to get all the XML nodes containing text (inside the element) that looks like "orange":

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

• the second request to do the same, but based on the attribute value:

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

1 text() XML (varchar(50)) SQL "% orange%".

. 2 (/@*) .

, UNION , .

, !