String Search in XQuery

  • Article

This topic provides sample queries that show how to search text in XML documents.

Examples

A. Find feature descriptions that contain the word "maintenance" in the product catalog

SELECT CatalogDescription.query('
     declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
    for $f in /p1:ProductDescription/p1:Features/*
     where contains(string($f), "maintenance")
     return
           $f ') as Result
FROM Production.ProductModel
WHERE ProductModelID=19

In the previous query, the where in the FLOWR expression filters the result of the for expression and returns only elements that satisfy the contains() condition.

This is the result:

<p1:Maintenance   
      xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
 <p1:NoOfYears>10</p1:NoOfYears>
 <p1:Description>maintenance contact available through your 
               dealer or any AdventureWorks retail store.</p1:Description>
</p1:Maintenance>

See Also

Concepts

xml Data Type

Other Resources

XQuery Against the xml Data Type

Help and Information

Getting SQL Server 2005 Assistance