Using Substring() in XML FLOWR Queries

Tonight I was monitoring the #sqlhelp hashtag on Twitter for a response to a question I asked when Randy Knight (Twitter) asked a question about using SUBSTRING in FLOWR statements with XML.

#sqlhelp Is there a way to do a SQL Type "LIKE" or "SUBSTRING" in the where clause of FLWOR statement? Need to evaluate just first n chars.

By the time I posted a response, Randy had figured out how to use the contains() function to solve his problem, but I am going to blog this because I am sure that I will need this at some point in the future, and it makes for a good blog post anyway.

Using an example from the Books Online topic for FLOWR, I mocked up the following example showing how to use substring() in the where clause of a FLOWR statement.

declare @x xml
set @x='<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >
<Location LocationID="L1" >
  <Step>Manu step 1 at Loc 1</Step>
  <Step>Manu step 2 at Loc 1</Step>
  <Step>Manu step 3 at Loc 1</Step>
</Location>
<Location LocationID="L2" >
  <Step>Manu step 1 at Loc 2</Step>
  <Step>Manu step 2 at Loc 2</Step>
  <Step>Manu step 3 at Loc 2</Step>
</Location>
</ManuInstructions>'
SELECT @x.query('
   for $step in /ManuInstructions/Location[1]/Step
   where substring(string($step), 11, 1) = "2"
   return string($step)
')

This outputs only a single node value where the substring() function evaluation matches the string value of 2.