Common XQuery mistakes…applied in XML DB

Do you read FAQ…?

Somehow I keep people reminding there is a FAQ URL on the XMLDB forum and even then people refuse to read those good examples… Anyway found two great posts I want to share and remember on this, my, web “notepad”. Besides the treewalker example, I tested the examples of those mentioned in the XQuery post on a Oracle 11.2 database.

As far as I could find the treewalker example is part of DOM V2 and not mandatory to implement but I wonder how I can get around the local() stuff, anyway, I will have to investigate a bit further if its just me being a novice in XQuery or that I am missing out on details/info. The XQuery post only demonstrates to me how powerful this extra query language is in an Oracle database and that it is time for me to learn this properly…

The posts that I was referring to:

…be aware of the use of the (double quote instead single quote), namespaces (indeed apparently always an issue) and using (::) in SQL*Plus… The (::) is needed in SQL*Plus to mark that the “;”  is not seen as direct processing instruction for SQL*Plus, but in this case, is for the XQuery engine.

The headlines follow the ones in the XQuery post…

You always need to do something else

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $n := 1
  4           return
  5             if ($n = 1) then
  6              "one"
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
from  dual
      *
ERROR at line 9:
ORA-19114: XPST0003 - error during parsing the
XQuery expression:
LPX-00801: XQuery syntax error at 'EOF'
5               "one"
-                    ^
 
SQL> ! oerr ORA 19114
 
19114, "XPST0003 - error during parsing the XQuery expression: %s"
 
// *Cause:  An error occurred during the parsing of the XQuery
            expression.
// *Action: Check the detailed error message for the possible causes.
 
SQL> !  oerr LPX 00801
 
00801, 00000, "XQuery syntax error at"
 
// *Cause:  Invalid XQuery query.
// *Action: Correct the query.
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $n := 1
  4           return
  5             if ($n = 1) then
  6              "one"
  7             else
  8              ()
  9          ' returning content)
 10          as "XMLQuery Output"
 11  from  dual;
 
XMLQuery Output
--------------------------------------------------
one

Dynamic evaluation is desired

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $xml := <foo><bar>text</bar><baz>text</baz></foo>
  4           for $el in ("bar", "baz")
  5           return
  6             $xml/$el/text()
  7          ' returning content)
  8          as "XMLQuery Output"
  9 from  dual;
from  dual
      *
ERROR at line 9:
ORA-19224: XPTY0004 - XQuery static type
mismatch: expected - node()* got - xs:string
 
SQL> ! oerr ORA 19224
 
19224, 00000, "XPTY0004 - XQuery static type mismatch: 
                          expected - %s got - %s "
 
// *Cause:  The expression could not be used because it's static type 
            is not appropriate for the context in which it was used.
// *Action: Fix the expression to be of the required type or add
            appropriate cast functions around the expression.

Curly, curly, curly braces

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             <test>{$a}</test>
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
 
XMLQuery Output
--------------------------------------------------
<test>test</test>

The desire to return multiple elements is strong

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             <el1>{$a}</el1><el2>{$a}</el2>
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
from  dual
      *
ERROR at line 8:
ORA-19114: XPST0003 - error during parsing the
XQuery expression:
LPX-00801: XQuery syntax error at '>'
4              <el1>{$a}</el1><el2>{$a}</el2>
-                                 ^
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             (<el1>{$a}</el1>,<el2>{$a}</el2>)
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
 
XMLQuery Output
--------------------------------------------------
<el1>test</el1><el2>test</el2>

attributes when you mean string

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $node := <meta name="author" content="James Fuller"/>
  4           return
  5             element {$node/@name}
  6                     {$node/@content}
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
<author content="James Fuller"></author>
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $node := <meta name="author" content="James Fuller"/>
  4           return
  5             element {$node/@name}
  6                     {fn:string($node/@content)}
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
<author>James Fuller</author>

Comparing things properly

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           let $b := "test"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
0
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "abc"
  4           let $b := "ab"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
1
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "ab"
  4           let $b := "abc"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
-1
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := (1,2,3)
  4           let $b := (3)
  5           return
  6           $a = $b
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
true
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "a"
  4           let $b := "a"
  5           return
  6           if( $a eq $b) then
  7             "string values matched"
  8           else
  9             "string values do not match"
 10          ' returning content)
 11          as "XMLQuery Output"
 12  from  dual;
 
XMLQuery Output
--------------------------------------------------
string values matched

Empty namespaces conundrum

SQL> set lines 100
SQL> set long 10000
SQL> set pages 5000
SQL> set feed on
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           declare default element namespace "http://www.w3.org/1999/xhtml"; (: :)
  4           declare namespace no-namespace = " "; (: :)
  5           let $xml := <html><body>
  6                       [no-namespace:element]This element has no namespace[/no]
  7                       </body></html>
  8           return
  9           $xml//*[namespace-uri() eq " "]
 10          ' returning content)
 11          as "XMLQuery Output"
 12  from  dual;
 
XMLQuery Output
----------------------------------------------------------------------------------------------------
[no-namespace:element xmlns:no-namespace=" "]This element has no namespace[/no-namespace:element]
 
1 row selected.

Had to use [ ] brackets here because I still don’t have found a plug-in that doesn’t mess with namespaces on WordPress sites and scrambles the output the moment it encounters namespace notations…

Anyway good to see that Oracle produces the same errors and results.
Next one to read for me ‘An Introduction to XQuery FLWOR expression‘…

8-)