Follow the rest of this series at the XQuery for the Non-Expert – X-Query Resources introduction post.
Within the value() and query() methods it is sometimes desirable to return a portion of a string. Similar to T-SQL, there is a substring function in XQuery that provides this functionality.
In this XQuery for the Non-Expert post we’ll be looking at the syntax for this function. Along with investigating how to implement the function in a couple of examples.
There are two sets of syntax for the substring() function. The first method of calling allows for setting the starting location in the string and returning all characters for the rest of the string. The second method sets a starting and ending location to return a portion of a string.
The syntax for each is the following:
fn:substring($sourceString as xs:string?, $startingLoc as as xs:decimal?) fn:substring($sourceString as xs:string?, $startingLoc as xs:decimal?, $length as xs:decimal?)
In the syntax above the arguments are:
As with previous posts, let’s start with a little bit of a setup script so that we can all follow along. This script will create the same tables and XML structures as before with a couple small changes.
Here’s the script:
IF OBJECT_ID('tempdb..#ValueExample') IS NOT NULL DROP TABLE #ValueExample GO CREATE TABLE #ValueExample ( XMLDocument xml ) INSERT INTO #ValueExample VALUES ('<Building type="skyscraper">Sears Tower <Floor level="1" Color="Lt-Blue"/> <Floor level="2" Color="Dk-Red"/> <Floor level="3" Color="Md-Yellow"> <Room number="3.1" /> <Room number="3.2" /> </Floor> <Floor level="4" Color="Lt-Purple"> <Room number="4.1" /> <Room number="4.2" /> </Floor> </Building>') SELECT XMLDocument FROM #ValueExample
Now that you have what we need to work with we’ll go through a couple examples.
For the first example, we’ll use the substring() function with the starting location and leave off the optional length value. We’ll shred the XML to the Floor element and then return the Color attribute. From there we’ll trim off the first three characters from the Color value and return the fourth through the last characters.
The query for this will be the following:
SELECT c.query('.') AS XMLFragment ,c.value('@Color','varchar(15)') AS Color ,c.value('substring(@Color,4)','varchar(15)') AS ColorFromFour FROM #ValueExample CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)
With the following output:
As you can see, by looking at the ColorFromFour column the value returned from the substring function does not return the first three characters.
In the next example, we’ll add in the length parameter to the substring function. Two columns will be returned in this example. The first will be ColorFromZero with the substring() function starting from zero. And then also, ColorFromOne with the substring() function starting from one.
Here’s the query:
SELECT c.query('.') AS XMLFragment ,c.value('@Color','varchar(15)') AS Color ,c.value('substring(@Color,0,3)','varchar(15)') AS ColorFromZero ,c.value('substring(@Color,1,3)','varchar(15)') AS ColorFromOne FROM #ValueExample CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)
And then the output:
So in this example, the starting position of less than one doesn’t change the first character returned or generate an error. It does though affect the value returned when the length is calculated.
The substring() function in XQuery operates very similar to its T-SQL counterpart. This post should have helped bridge the small difference with the optional parameter and demonstrated a couple ways in which this can be used. As with the other posts, if anything in this post needs further clarity please comment below.