Yet another SQL Server enthusiast

2015-04-15

XQuery: Comparison operators for equality

Filed under: sql server,xml,xquery — admin @ 7:06 AM

SQL Server supports a subset of the XQuery language. One of features implemented within SQL Server XQuery are comparison operators. There are two kinds of comparison operators:

  1. Operators for sequences comparison (=, !=, <, <=, >, >=) and
  2. Operators for singletons comparison (named also scalar values; eq, ne, lt, le, gt, ge).

Two operators are available for equality comparison:  = and eq .

First operator (=) can be used to compare sequences and / or singleton values / operands. Second operator (eq) can be used only with singleton values / operands. When two sequences are compared and both sequences have at least one common value the result is true, otherwise (there is no common value) the result is false.

Example 1: sequences and = operator
Code Snippet
PRINT 'Test #1:';
DECLARE @x XML = N'';

SELECT '(10, 20, 30) = 10' AS XQuery,    @x.query('(10, 20, 30) = 10') AS Result
UNION ALL
SELECT '(10, 20, 30) = 100',            @x.query('(10, 20, 30) = 100')
UNION ALL
SELECT '(10, 20, 30) = (10)',            @x.query('(10, 20, 30) = (10)')
UNION ALL
SELECT '(10, 20, 30) = (10,30)',        @x.query('(10, 20, 30) = (10, 30)')
UNION ALL
SELECT '(10, 20, 30) = (10, 20, 30, 40)',@x.query('(10, 20, 30) = (10, 20, 30, 40)')
UNION ALL
SELECT '(10, 20, 30) = (25, 40)',        @x.query('(10, 20, 30) = (25, 40)')
/*
Output:

Test #1:
XQuery                          Result    Comment
——————————- ——    ——
(10, 20, 30) = 10               true    seq. vs value
(10, 20, 30) = 100              false    seq. vs value
(10, 20, 30) = (10)             true    seq. vs seq.
(10, 20, 30) = (10,30)          true    seq. vs seq.
(10, 20, 30) = (10, 20, 30, 40) true    seq. vs seq.
(10, 20, 30) = (25, 40)         false    seq. vs seq.
*/

Example 2: singleton values and = operator
Code Snippet
PRINT 'Test #2:';
SELECT '10 = 10'XQuery,    @x.query('10 = 10') AS Result
UNION ALL
SELECT '10 = 20',        @x.query('10 = 20')
/*
Output:

Test #2:
XQuery  Result
——- ——
10 = 10 true
10 = 20 false
*/

Example 3: sequences and eq operator
Code Snippet
PRINT 'Test #3:';
GO
DECLARE @x XML = N'';
SELECT '(10, 20, 30) eq 10'XQuery,            @x.query('(10, 20, 30) eq 10') AS Result
GO
/*
Output:

Test #3:
Msg 2389, Level 16, State 1, Line 15
XQuery [query()]: 'eq' requires a singleton (or empty sequence), found operand of type 'xs:integer +'
*/

Example 4 singleton values and eq operator
Code Snippet
DECLARE @x XML = N'';
SELECT '(10, 20, 30)[1] eq 10 'XQuery,    @x.query('(10, 20, 30)[1] eq 10') AS Result
UNION ALL
SELECT '(10, 20, 30)[1] eq 100',        @x.query('(10, 20, 30)[1] eq 100')
UNION ALL
SELECT '(10, 20, 30)[2] eq 10 ',        @x.query('(10, 20, 30)[2] eq 10')
UNION ALL
SELECT '(10, 20, 30)[2] eq 100',        @x.query('(10, 20, 30)[2] eq 100')
GO
/*
Output:

Test #4:
XQuery                 Result
———————- ——
(10, 20, 30)[1] eq 10  true
(10, 20, 30)[1] eq 100 false
(10, 20, 30)[2] eq 10  false
(10, 20, 30)[2] eq 100 false
*/

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: