String Search in XQuery
Collapse the table of content
Expand the table of content

String Search in XQuery

 

Applies To: SQL Server

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

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

SELECT CatalogDescription.query('  
     declare namespace p1="http://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="http://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>  

XML Data (SQL Server)
XQuery Language Reference (SQL Server)

Community Additions

ADD
Show:
© 2016 Microsoft