Yet another SQL Server enthusiast

2015-04-15

XQuery: Comparison operators for equality

Filed under: sql server,xml,xquery — sa @ 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

2015-04-09

How to get full path of every XML element using XQuery and recursive Common Table Expressions

Filed under: sql server,xml,xquery — sa @ 6:36 AM

Solution:

Code Snippet
DECLARE @x XML = N'
<A>
    <B>
        <C>Text1</C>
        <D>Text2</D>
        <E>
            <F>Text3</F>
        </E>
    </B>
    <G>Text4</G>
</A>';

  WITH XmlRec
  AS (
    SELECT    a.XmlCol.query('.') AS CurrentElement,
            a.XmlCol.value('(text())[1]', 'NVARCHAR(100)') AS CurrentElementValue,
            a.XmlCol.value('local-name(.)', 'SYSNAME') AS CurrentElementName,
            CONVERT(NVARCHAR(4000), a.XmlCol.value('local-name(.)', 'SYSNAME')) AS CurrentElementPath
    FROM    @x.nodes('*') a(XmlCol)
    UNION ALL
    SELECT    b.XmlCol.query('.') AS CurrentElement,
            b.XmlCol.value('(text())[1]', 'NVARCHAR(100)') AS CurrentElementValue,
            b.XmlCol.value('local-name(.)', 'SYSNAME') AS CurrentElementName,
            rec.CurrentElementPath + '/' + CONVERT(NVARCHAR(4000), b.XmlCol.value('local-name(.)', 'SYSNAME')) AS CurrentElementPath
    FROM    XmlRec rec
    CROSS APPLY rec.CurrentElement.nodes('*/*') b(XmlCol)
  )
  SELECT    r.*
  FROM        XmlRec r
  OPTION (MAXRECURSION 100)

Results:

XML.Element.Full.Path

Blog at WordPress.com.