XQuery Functions
By : Kasim Wirama, MCDBA, MCITP
XQuery has built-in functions such as min, max, true, false, not, sum, substring, concat, contains, etc. They are under namespace fn:. You can prefix those functions with fn: namespace but it is not mandatory. Let’s see by example those functions below :
1. Sum
It sum all values.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:sum((3,2,5))'); -- > result : 10
2. Substring (s,m,n)
It return substring of s string, beginning from m position with n character(s). if n is not specified it will take from m position until end of s string
Example :
declare @x xml;
set @x = '';
select @x.query('fn:substring("abcde",2)');
select @x.query('fn:substring("abcde",2,3)');
3. String-length
It returns string length including space.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:string-length("abcde ")');
4. String
It converts any input to string return result.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:string(1234)');
5. Not
It will negate Boolean input. When Boolean input is false, it returns true, and vice versa.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:not(fn:false())');
6. Round
It will round to nearest 0.5 fraction.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:round(-9.5)');
7. Min
Example :
declare @x xml;
set @x = '';
select @x.query('fn:min((4,2,6,4,2,8,5,14,-44))');
8. Max
Example :
declare @x xml;
set @x = '';
select @x.query('fn:max((4,2,6,4,2,8,5,14,-44))');
9. True
returns true boolean result
Example :
declare @x xml;
set @x = '';
select @x.query('fn:true()');
10. False
It returns false Boolean result
Example :
declare @x xml;
set @x = '';
select @x.query('fn:false()');
11. Empty
It returns true when input parameter is empty and false if it is not empty.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:empty(( ))');
12. Distinct-values
It filters out duplicated value.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:distinct-values((1,1,2,3,4))');
13. Data
It returns all heterogenous type of data.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:data((12.46,"testing"))');
14. Count
It returns number of members in input parameter.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:count((1,1,2,3,4))');
15. Concat
It returns strings in concatenated form.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:concat("a","b","c")');
16. Contains (s1, s2)
It returns true if s2 string is in s1 string.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:contains("animal","ni")');
17. Avg
It returns average value.
Example :
declare @x xml;
set @x = '';
select @x.query('fn:avg((10,20,50))')
18. Floor (s)
It returns numeric value without decimal and it will return value less then s
Example :
declare @x xml;
set @x = '';
select @x.query('fn:floor(-1.1)');
select @x.query('fn:floor(1.1)');
19. Ceiling
It returns numeric value without decimal and it will return value less then s
Example :
declare @x xml;
set @x = '';
select @x.query('fn:ceiling(-1.1)');
select @x.query('fn: ceiling (1.1)');