Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
String Search in XQuery

String Search in XQuery

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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>

Community Additions

ADD
Show:
© 2015 Microsoft