XML Values
The XML type holds XML documents and fragments of them, as SQL/XML (ISO/IEC 9075-14) defines it. XMLPARSE makes an XML value from text, and XMLSERIALIZE writes one back as text. XMLDOCUMENT makes a document node of a value, XMLCAST casts SQL values to XML and back, and IS DOCUMENT and IS CONTENT test what a value is. XMLQUERY, XMLEXISTS and XMLTABLE query XML values with XQuery. XMLMAP maps a table, a schema or the catalog to XML and to an XML Schema document. XMLVALIDATE validates a value by a registered XML Schema, IS VALID tests whether it is valid by one, and XQuery's validate expression validates nodes by them in a query.
The type
<XML type> ::= XML [ ( <XML type modifier> ) ]
<XML type modifier> ::=
DOCUMENT [ ( <secondary XML type modifier> ) ]
| CONTENT [ ( <secondary XML type modifier> ) ]
| SEQUENCE
<secondary XML type modifier> ::=
ANY
| UNTYPED
| XMLSCHEMA <XML valid according to what> [ <XML valid element clause> ]
| Type | Holds |
|---|---|
| XML(DOCUMENT) | A document: one root element, with comments and processing instructions beside it. |
| XML(CONTENT) | A document node of any children: elements, text, comments and processing instructions. |
| XML(SEQUENCE) | Any XML value. XML alone is XML(SEQUENCE). |
UNTYPED, ANY and XMLSCHEMA say whether a value may carry types from an XML Schema. Every value XMLPARSE makes is UNTYPED. DOCUMENT and CONTENT alone are ANY. An UNTYPED column takes values of an UNTYPED type only; an ANY or SEQUENCE column takes any XML value. A type of XMLSCHEMA holds values valid by a registered XML Schema, which The XMLSCHEMA types describes.
A value stored in a DOCUMENT column that is not a document is refused with SQLSTATE 2200L. A short value is kept in its row, and a long one apart from it as large objects are, logged and recovered with them. A value inside an array, a multiset, a row or a structured value is kept whole in that value's row, up to the limit of a value's stored form. The column store keeps no XML value, nor any inside another value.
XML values are not comparable. =, IN, DISTINCT, GROUP BY, ORDER BY,
MAX, MIN, UNION without ALL, a NATURAL or USING join on an XML column,
and a key or an index of one are refused (SQLCODE -103), but an index of
an XML method (XML indexes).
The Information Schema shows a type's modifiers in XML_PRIMARY_MODIFIER and XML_SECONDARY_MODIFIER, the second null of SEQUENCE. A parameter or result of a routine of an XML type is passed by reference: XML_PASSING_MECHANISM in PARAMETERS and XML_RETURN_MECHANISM in ROUTINES are BY REF.
CREATE TABLE PAGES (ID INTEGER, BODY XML(DOCUMENT(UNTYPED)), NOTES XML);
SELECT COLUMN_NAME, DATA_TYPE, XML_PRIMARY_MODIFIER, XML_SECONDARY_MODIFIER FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PAGES' ORDER BY ORDINAL_POSITION;
OK
COLUMN_NAME | DATA_TYPE | XML_PRIMARY_MODIFIER | XML_SECONDARY_MODIFIER
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
ID | INTEGER | NULL | NULL
BODY | XML | DOCUMENT | UNTYPED
NOTES | XML | SEQUENCE | NULL
(3 rows)
XMLPARSE
XMLPARSE ( { DOCUMENT | CONTENT } <string value expression>
{ PRESERVE | STRIP } WHITESPACE )
XMLPARSE reads a character string, a binary string or a large object as XML 1.0 or XML 1.1, with namespaces. DOCUMENT requires a document, else SQLSTATE 2200M. CONTENT takes a document or any well-formed fragment, else 2200N. Its result is XML(DOCUMENT(UNTYPED)) or XML(CONTENT(UNTYPED)).
STRIP WHITESPACE removes text that is white space alone, except within
an element whose xml:space is preserve. PRESERVE WHITESPACE keeps
every character.
The internal DTD subset is read: its entities are expanded and its attribute defaults added. No external DTD or external entity is read, so a parse never opens a file or a URL. A reference to an entity that would need one fails as the parse does.
A binary string is read by its byte order mark and the encoding its XML declaration names: UTF-8, UTF-16, UTF-16BE, UTF-16LE, ISO-8859-1 or US-ASCII.
XMLSERIALIZE
XMLSERIALIZE ( [ DOCUMENT | CONTENT ] <XML value expression> AS <data type>
[ ENCODING { UTF8 | UTF16 } ]
[ VERSION '1.0' | VERSION '1.1' ]
[ INCLUDING XMLDECLARATION | EXCLUDING XMLDECLARATION ] )
XMLSERIALIZE writes a value as text of the character or binary string type AS names. CONTENT is the default. DOCUMENT requires a document, else SQLSTATE 2200L. ENCODING, of a binary string alone, is UTF8 by default; UTF16 writes big-endian after a byte order mark. VERSION is '1.0' by default.
An XML declaration is written with INCLUDING XMLDECLARATION, and for a DOCUMENT of version '1.1'. A result longer than its type fails with SQLSTATE 22001. Characters a parse would change, such as a carriage return, are written as character references.
XMLSERIALIZE is how the result of a query is serialized. It writes by XML's output method, with no indentation, CDATA section, document type declaration or standalone declaration, and undeclares no namespace prefix, in version '1.1' too, so an element copied without its parent's prefixes is written within their scope. A value it cannot write, such as an attribute alone or a character its version has not, fails with SQLSTATE 2200W, naming the serialization error.
VALUES (XMLSERIALIZE(XMLQUERY('declare copy-namespaces preserve, no-inherit; let $b := <b/> return <x:a xmlns:x="u">{$b}</x:a>' RETURNING SEQUENCE BY REF EMPTY ON EMPTY) AS VARCHAR(60) VERSION '1.1'));
VALUES (XMLSERIALIZE(XMLQUERY('attribute a {1}' RETURNING SEQUENCE BY REF EMPTY ON EMPTY) AS VARCHAR(60)));
--------------------------------------------------------------
<x:a xmlns:x="u"><b/></x:a>
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2200W): the XML value cannot be serialized: it holds an attribute that is no element's [Serialization §2] err:SENR0001 [ISO/IEC 9075-14:2006 draft §10.15 GR11a i]
A value selected without XMLSERIALIZE is shown as its serialization.
CREATE TABLE ORDERS (ID INTEGER, BODY XML(DOCUMENT));
INSERT INTO ORDERS VALUES (1, XMLPARSE(DOCUMENT '<order no="17"><item>tea</item> <item>cups</item></order>' STRIP WHITESPACE));
SELECT ID, XMLSERIALIZE(DOCUMENT BODY AS VARCHAR(100)) FROM ORDERS;
SELECT XMLSERIALIZE(BODY AS VARCHAR(100) INCLUDING XMLDECLARATION) FROM ORDERS;
INSERT INTO ORDERS VALUES (2, XMLPARSE(CONTENT '<a/><b/>' PRESERVE WHITESPACE));
SELECT ID FROM ORDERS ORDER BY BODY;
OK
OK
ID |
--------------+--------------------------------------------------------------
1 | <order no="17"><item>tea</item><item>cups</item></order>
(1 row)
--------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?><order no="17"><item>tea</item><item>cups</item></order>
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2200L): the value of XML(DOCUMENT(ANY)) is not an XML document [ISO/IEC 9075:1992 §13.8 GR4b iii; ISO/IEC 9075-14:2006 draft §10.2 GR1a]
turborel: SQLCODE -103 (SQLSTATE 2A000): ORDER BY orders values of XML(DOCUMENT(ANY)), and XML values are not comparable [ISO/IEC 9075-14:2006 draft §10.11 SR1]
CAST
CAST takes an XML value to another XML type, and nothing else to or from one. A value cast to a DOCUMENT or CONTENT type is checked as one stored in such a column. BY REF, the default, keeps the value's nodes, and casts to an UNTYPED type only from an UNTYPED one, and to a type of XMLSCHEMA only from one its column would take. BY VALUE copies them: to an UNTYPED type without their types, to a type of XMLSCHEMA validating them by its registered XML Schema, as XMLVALIDATE does.
CAST ( <value expression> AS <XML type> [ BY REF | BY VALUE ] )
INSERT INTO ORDERS VALUES (3, XMLPARSE(DOCUMENT '<a/>' PRESERVE WHITESPACE));
SELECT XMLSERIALIZE(BODY AS VARBINARY(40) ENCODING UTF16) FROM ORDERS WHERE ID = 3;
SELECT XMLSERIALIZE(CAST(BODY AS XML(CONTENT(UNTYPED)) BY VALUE) AS VARCHAR(80)) FROM ORDERS;
SELECT CAST(BODY AS VARCHAR(80)) FROM ORDERS;
OK
--------------------------------------------------------------
FEFF003C0061002F003E
(1 row)
--------------------------------------------------------------
<order no="17"><item>tea</item><item>cups</item></order>
<a/>
(2 rows)
turborel: SQLCODE -103 (SQLSTATE 2A000): CAST of XML(DOCUMENT(ANY)) to CHARACTER VARYING(80) is not valid [ISO/IEC 9075-14:2006 draft §6.5 SR3] (at character 7)
XMLCAST
XMLCAST ( { <value expression> | NULL } AS { <data type> | <domain name> }
[ BY REF | BY VALUE ] )
XMLCAST casts a value of an SQL type to an XML type, or an XML value to an SQL type. Between two XML types it is CAST. No SQL value is cast to a type of XMLSCHEMA.
An SQL value cast to XML(CONTENT) becomes a document node holding its
text: a string's characters, a number as CAST writes it, a truth value
as true or false, a date and time with a T between them, an interval
as an XML duration such as P01Y02M, and a binary string in hex, or in
base64 where XMLBINARY USING BASE64 is in scope
(XML options). Cast
to XML(SEQUENCE), it becomes one atomic value of the XML Schema type its
type maps to, in that type's canonical form: xs:decimal 1.5 for
NUMERIC 1.50, xdt:yearMonthDuration P1Y2M, xs:hexBinary or, of
BASE64, xs:base64Binary for a binary string. A character no XML 1.1
character fails with SQLSTATE 0N002. No SQL value is cast to
XML(DOCUMENT). No array, multiset or row is cast either, but a value of
a distinct type whose source is an array or a multiset is cast to
XML(CONTENT), written as XMLELEMENT writes it as content; cast to
XML(SEQUENCE), it is refused (SQLCODE -900).
An XML value cast to an SQL type gives the text of its one item: a document node's children, an element's text, or, of an element a validation typed, its typed value (XMLVALIDATE). That text is read as a value of the XML Schema type the SQL type maps to, restricted as the table below shows, its white space collapsed first unless the type is a string type. The value is then cast to the SQL type. A value the restriction does not take fails with SQLSTATE 10000 where CAST would round, pad or cut it: CHAR(5) takes text of five characters alone, and NUMERIC(6,1) no second digit of fraction.
| SQL type | XML Schema type, and what it takes |
|---|---|
| CHARACTER(n) | xs:string of n characters; at most n of a length in OCTETS |
| CHARACTER VARYING(n), CLOB(n) | xs:string of at most n characters |
| BINARY(n), VARBINARY(n), BLOB(n) | xs:hexBinary of at most n octets; xs:base64Binary where XMLBINARY USING BASE64 is in scope |
| NUMERIC(p,s), DECIMAL(p,s) | xs:decimal of at most p digits, s after the point |
| INTEGER, SMALLINT, BIGINT | xs:integer within the type's range |
| REAL, DOUBLE PRECISION, FLOAT | xs:float or xs:double |
| BOOLEAN | xs:boolean |
| DATE | xs:date of four digits of year and two of month and day |
| TIME(p), TIMESTAMP(p) | xs:time, xs:dateTime of at most p digits of fraction, zeros ending it not counted; WITH TIME ZONE, with a time zone |
| INTERVAL | xdt:yearMonthDuration or xdt:dayTimeDuration, each field written as XMLCAST writes it: the first of the leading precision's digits, the others of two, the seconds' fraction of the fractional precision's, none if it is 0: PT1234.500S for INTERVAL SECOND(4, 3), PT01H02M03S for INTERVAL HOUR TO SECOND(0) |
A date or time with a time zone is moved to UTC for a type without one. A time or timestamp is checked as XMLCAST writes a value of its type, its fraction padded with zeros, so TIMESTAMP takes 14:30:00 and 14:30:00.5, and TIMESTAMP(0) refuses 14:30:00.5. Text that is no such value, or more than one item, fails with SQLSTATE 10000; a value with no item is null.
SELECT ID, XMLCAST(BODY AS VARCHAR(20)) FROM ORDERS ORDER BY ID;
VALUES (XMLSERIALIZE(XMLCAST(CAST(1.50 AS NUMERIC(5,2)) AS XML(CONTENT)) AS VARCHAR(20)), XMLSERIALIZE(XMLCAST(CAST(1.50 AS NUMERIC(5,2)) AS XML) AS VARCHAR(20)));
VALUES (XMLCAST(XMLPARSE(CONTENT '<total currency="EUR"> 17.25 </total>' PRESERVE WHITESPACE) AS NUMERIC(6,2)));
VALUES (XMLCAST(XMLPARSE(CONTENT '<due>2026-11-17T09:30:00-05:00</due>' PRESERVE WHITESPACE) AS TIMESTAMP));
VALUES (XMLCAST(XMLPARSE(CONTENT '<total currency="EUR"> 17.25 </total>' PRESERVE WHITESPACE) AS NUMERIC(6,1)));
VALUES (XMLCAST(XMLPARSE(CONTENT '<qty>many</qty>' PRESERVE WHITESPACE) AS INTEGER));
ID |
--------------+----------------------
1 | teacups
3 |
(2 rows)
|
----------------------+----------------------
1.50 | 1.5
(1 row)
----------
17.25
(1 row)
----------------------------
2026-11-17 14:30:00.000000
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error: '17.25' is not valid for NUMERIC(6,1): its 2 digits break its fractionDigits [Schema2 §4.3.12.3] [Schema2 §4.1.4] [XQueryFO §17.1.1] err:FORG0001 [ISO/IEC 9075-14:2006 draft §6.6 GR3d vi, GR4j]
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error: 'many' is not valid for INTEGER: it is no literal of xs:integer [Schema2 §4.1.4] [XQueryFO §17.1.1] err:FORG0001 [ISO/IEC 9075-14:2006 draft §6.6 GR3d vi, GR4j]
XMLDOCUMENT
XMLDOCUMENT ( <XML value expression> [ RETURNING { CONTENT | SEQUENCE } ] )
XMLDOCUMENT makes a new document node whose children are copies of the value's nodes; a document node of the value gives its children instead. Adjacent atomic values become one text node, a space apart, and adjacent text is joined. An attribute standing alone among the value's items fails with SQLSTATE 10000. XMLDOCUMENT of the null value is null.
RETURNING CONTENT, the default, makes a value of XML(CONTENT(UNTYPED)); RETURNING SEQUENCE one of XML(SEQUENCE). A document node is not always a document, so a DOCUMENT column refuses one that is not, as the example below shows.
IS DOCUMENT and IS CONTENT
<XML value expression> IS [ NOT ] DOCUMENT
<XML value expression> IS [ NOT ] CONTENT
IS DOCUMENT is true of a document: one document node whose children are one element, with comments and processing instructions beside it. IS CONTENT is true of one document node. Each is unknown of the null value. Either may follow WHEN in a simple CASE, testing its operand.
CREATE TABLE NOTES (ID INTEGER, BODY XML);
INSERT INTO NOTES VALUES (1, XMLPARSE(DOCUMENT '<a/>' PRESERVE WHITESPACE)), (2, XMLPARSE(CONTENT 'note <b/>' PRESERVE WHITESPACE)), (3, NULL);
SELECT ID, BODY IS DOCUMENT, BODY IS CONTENT FROM NOTES ORDER BY ID;
SELECT ID, CASE BODY WHEN IS DOCUMENT THEN 'document' WHEN IS CONTENT THEN 'content' END FROM NOTES ORDER BY ID;
SELECT XMLSERIALIZE(XMLDOCUMENT(BODY) AS VARCHAR(40)) FROM NOTES WHERE ID = 2;
INSERT INTO ORDERS SELECT 4, XMLDOCUMENT(BODY) FROM NOTES WHERE ID = 2;
OK
OK
ID | |
--------------+-------+-------
1 | TRUE | TRUE
2 | FALSE | TRUE
3 | NULL | NULL
(3 rows)
ID |
--------------+----------
1 | document
2 | content
3 | NULL
(3 rows)
------------------------------------------
note <b/>
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2200L): the value of XML(DOCUMENT(ANY)) is not an XML document [ISO/IEC 9075:1992 §13.8 GR4b iii; ISO/IEC 9075-14:2006 draft §10.2 GR1a]
Publishing XML
The publishing operators make XML values of SQL values.
XMLELEMENT ( NAME <XML element name>
[ , XMLNAMESPACES ( <XML namespace declaration item> [ , ... ] ) ]
[ , XMLATTRIBUTES ( <value expression> [ AS <XML attribute name> ] [ , ... ] ) ]
[ { , <value expression> }... [ OPTION <XML content option> ] ]
[ RETURNING { CONTENT | SEQUENCE } ] )
XMLFOREST ( [ XMLNAMESPACES ( ... ) , ] <value expression> [ AS <forest element name> ] [ , ... ]
[ OPTION <XML content option> ] [ RETURNING { CONTENT | SEQUENCE } ] )
XMLCONCAT ( <XML value expression> , <XML value expression> [ , ... ] [ RETURNING { CONTENT | SEQUENCE } ] )
XMLCOMMENT ( <character value expression> [ RETURNING { CONTENT | SEQUENCE } ] )
XMLPI ( NAME <XML PI target> [ , <character value expression> ] [ RETURNING { CONTENT | SEQUENCE } ] )
XMLTEXT ( <character value expression> [ RETURNING { CONTENT | SEQUENCE } ] )
<XML namespace declaration item> ::= <XML namespace URI> AS <XML namespace prefix>
| DEFAULT <XML namespace URI> | NO DEFAULT
<XML content option> ::= NULL ON NULL | EMPTY ON NULL | ABSENT ON NULL
| NIL ON NULL | NIL ON NO CONTENT
XMLELEMENT makes an element. Its name, and each attribute's, is an identifier: a regular identifier's in upper case, a delimited one's as written, with a prefix if it has a colon. An attribute or a forest element named by no AS must be a column, and takes the column's name. An attribute whose value is null is left out. Each value of the content becomes the element's content: an XML value's nodes, copied, or another value's text. A character string is escaped as text; a number, a datetime, an interval or a truth value is written as XML Schema writes it; a binary string is written in hex, or in base64 where XMLBINARY USING BASE64 is in scope. An attribute's value is of a predefined type other than XML, or of a distinct type of one.
Content may be an array or a multiset too, or a value of a distinct type
whose source is one. Each element becomes an element named element,
holding its own value written as content is; a null element becomes
<element xsi:nil="true"/>, which declares the prefix xsi. An element
that is a row writes an element of each field that is not null, named by
the field's name as an XML name, its characters an XML name may not hold
escaped as _xHHHH_. An element that is an XML value is written as
XMLSERIALIZE writes it. A collection of XML(SEQUENCE), XML alone among
them, of a structured or a reference type, or of DECFLOAT, and content
of a row, are refused (SQLCODE -103).
The content option says what an element of content all null is: EMPTY
ON NULL, the default, an empty element; NULL ON NULL the null value;
ABSENT ON NULL nothing; NIL ON NULL an empty element with the attribute
xsi:nil="true". NIL ON NO CONTENT gives that attribute to an element
with no element or text in its content.
XMLFOREST makes an element of each value, named as attributes are, and gives them in order. Its content option applies to each element; NULL ON NULL is its default, so a forest of null values alone is null.
XMLNAMESPACES binds prefixes to namespaces for the names of the element
or forest it is in and of the operators within it, inside those a query,
a statement or a definition declares
(XML options). DEFAULT names the
namespace of a name with no prefix, and NO DEFAULT none. The prefixes
xml, xs, xsi and sqlxml are bound unless declared otherwise.
Each element declares the namespaces its names use.
XMLCONCAT gives the items of each value that is not null, in order, and
is null if every value is. XMLCOMMENT makes a comment, which may not
hold two hyphens together or end with one (SQLSTATE 2200S). XMLPI makes
a processing instruction of a target and text, its leading white space
removed, which may not hold ?> (SQLSTATE 2200T); its target may not be
xml. XMLTEXT makes a text node. A character no XML 1.1 text holds is
SQLSTATE 0N002.
Each operator is RETURNING CONTENT by default: its value is a document node of what it makes, of XML(CONTENT(UNTYPED)), which a DOCUMENT or CONTENT column takes. RETURNING SEQUENCE gives what it makes itself, of XML(SEQUENCE).
CREATE TABLE STAFF (ID INTEGER, NAME VARCHAR(20), PAY DECIMAL(7,2), PHOTO VARBINARY(4));
INSERT INTO STAFF VALUES (1, 'Ann', 1200.50, X'0FB7'), (2, 'Bo & Co', NULL, NULL);
SELECT XMLSERIALIZE(XMLELEMENT(NAME "emp", XMLATTRIBUTES(ID AS "id"), XMLELEMENT(NAME "name", NAME), XMLFOREST(PAY, PHOTO)) AS VARCHAR(100)) FROM STAFF ORDER BY ID;
SELECT XMLSERIALIZE(XMLELEMENT(NAME emp, PAY OPTION NIL ON NULL) AS VARCHAR(100)) FROM STAFF ORDER BY ID;
SELECT XMLSERIALIZE(XMLELEMENT(NAME "h:p", XMLNAMESPACES('http://www.w3.org/1999/xhtml' AS "h"), XMLCOMMENT('pay'), XMLTEXT('see '), XMLPI(NAME "page", 'next')) AS VARCHAR(200)) FROM STAFF WHERE ID = 1;
SELECT XMLSERIALIZE(XMLCONCAT(XMLELEMENT(NAME "a"), XMLELEMENT(NAME "b")) AS VARCHAR(20)) FROM STAFF WHERE ID = 1;
SELECT XMLCOMMENT('a--b') FROM STAFF;
OK
OK
--------------------------------------------------------------
<emp id="1"><name>Ann</name><PAY>1200.50</PAY><PHOTO>0FB7</PHOTO></emp>
<emp id="2"><name>Bo & Co</name></emp>
(2 rows)
--------------------------------------------------------------
<EMP>1200.50</EMP>
<EMP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
(2 rows)
--------------------------------------------------------------
<h:p xmlns:h="http://www.w3.org/1999/xhtml"><!--pay-->see <?page next?></h:p>
(1 row)
----------------------
<a/><b/>
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2200S): the comment holds two hyphens together, ends with one, or holds a character XML does not [XML 1.1 §2.5 [15]] [ISO/IEC 9075-14:2006 draft §6.11 GR3]
Arrays, multisets and rows inside them are published as their elements:
SELECT XMLSERIALIZE(XMLELEMENT(NAME "pays", ARRAY[PAY, PAY * 2]) AS VARCHAR(200)) FROM STAFF ORDER BY ID;
SELECT XMLSERIALIZE(XMLFOREST(MULTISET[NAME] AS "names", ARRAY[CAST(ROW(ID, NAME) AS ROW("id" INTEGER, "full name" VARCHAR(20)))] AS "rows") AS VARCHAR(200)) FROM STAFF WHERE ID = 1;
SELECT XMLELEMENT(NAME "r", ROW(ID, NAME)) FROM STAFF;
--------------------------------------------------------------
<pays><element>1200.50</element><element>2401.00</element></pays>
<pays><element xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/><element xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/></pays>
(2 rows)
--------------------------------------------------------------
<names><element>Ann</element></names><rows><element><id>1</id><full_x0020_name>Ann</full_x0020_name></element></rows>
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): a value of ROW(F1 INTEGER, F2 CHARACTER VARYING(20)) is not mapped to XML here [ISO/IEC 9075-14:2006 draft §6.14 SR7] (at character 7)
XMLAGG
XMLAGG ( <XML value expression> [ ORDER BY <sort specification list> ]
[ RETURNING { CONTENT | SEQUENCE } ] )
XMLAGG is a set function. It gives the XML values of a group, in the order of its sort keys, one after another; values that are null are left out, and a group of no value not null gives the null value. Without ORDER BY the order is that in which the rows are read. RETURNING CONTENT, the default, makes a document node of them; RETURNING SEQUENCE gives them as a sequence. XMLAGG may be the aggregate of a window function. Values that together hold more than 64 MiB fail with SQLSTATE 2200R.
SELECT XMLSERIALIZE(XMLELEMENT(NAME "staff", XMLAGG(XMLELEMENT(NAME "emp", XMLATTRIBUTES(ID AS "id"), NAME) ORDER BY NAME DESC)) AS VARCHAR(200)) FROM STAFF;
--------------------------------------------------------------
<staff><emp id="2">Bo & Co</emp><emp id="1">Ann</emp></staff>
(1 row)
XML in other types
An array or a multiset may hold XML values, of any XML type or of a
distinct type of one, and so may a field of a row and an attribute of a
structured type. A distinct type may have an XML type as its source:
CREATE TYPE LEAF AS XML(DOCUMENT(ANY)) FINAL. Its function to its
source is named XML, as XML(COVER) below, and CAST takes a value to the
type and back. A routine's parameter or result may be of any of them.
Each XML value inside is assigned as one stored in a column of its type is, so an array of XML(DOCUMENT) refuses an element that is not a document (SQLSTATE 2200L). None of these is ever compared: an array, multiset, row or distinct type holding XML is refused wherever an XML value is (SQLCODE -103), and so are DISTINCT, SET, IS A SET, MEMBER, SUBMULTISET and the multiset operators but MULTISET UNION ALL over a multiset of XML.
CREATE TYPE LEAF AS XML(DOCUMENT(ANY)) FINAL;
CREATE TABLE BOOKS (ID INTEGER, PAGES XML(CONTENT(ANY)) ARRAY[10], COVER LEAF, META ROW(TITLE VARCHAR(20), BLURB XML(CONTENT(ANY))));
INSERT INTO BOOKS VALUES (1, ARRAY[XMLPARSE(CONTENT '<p>One</p>' PRESERVE WHITESPACE), XMLPARSE(CONTENT '<p>Two</p>' PRESERVE WHITESPACE)], CAST(XMLPARSE(DOCUMENT '<cover/>' PRESERVE WHITESPACE) AS LEAF), ROW('Tales', XMLPARSE(CONTENT 'A <b>bold</b> start' PRESERVE WHITESPACE)));
SELECT ID, CARDINALITY(PAGES), XMLSERIALIZE(PAGES[2] AS VARCHAR(20)), XMLSERIALIZE(BOOKS.META.BLURB AS VARCHAR(30)) FROM BOOKS;
SELECT XMLSERIALIZE(XML(COVER) AS VARCHAR(20)) FROM BOOKS;
INSERT INTO BOOKS (ID, COVER) VALUES (2, CAST(XMLPARSE(CONTENT 'no root' PRESERVE WHITESPACE) AS LEAF));
SELECT ID FROM BOOKS WHERE PAGES = PAGES;
OK
OK
OK
ID | | |
--------------+--------------+----------------------+--------------------------------
1 | 2 | <p>Two</p> | A <b>bold</b> start
(1 row)
----------------------
<cover/>
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2200L): the value of XML(DOCUMENT(ANY)) is not an XML document [ISO/IEC 9075-14:2006 draft §6.5 GR1a]
turborel: SQLCODE -103 (SQLSTATE 2A000): the comparison compares values of XML(CONTENT(ANY)) ARRAY[10], and XML values are not comparable [ISO/IEC 9075-14:2006 draft §10.8 SR1] (at character 27)
Querying XML
XMLQUERY, XMLEXISTS and XMLTABLE evaluate a query of XQuery 1.0, the language of the W3C Candidate Recommendation of 3 November 2005, over XML values and SQL values passed to it.
XMLQUERY ( <XQuery expression>
[ PASSING { BY REF | BY VALUE } <argument> [ , <argument> ]... ]
[ RETURNING { CONTENT | SEQUENCE } [ BY REF | BY VALUE ] ]
{ NULL | EMPTY } ON EMPTY )
XMLEXISTS ( <XQuery expression> [ PASSING { BY REF | BY VALUE } <argument> [ , ... ] ] )
XMLTABLE ( [ XMLNAMESPACES ( ... ) , ] <row pattern>
[ PASSING { BY REF | BY VALUE } <argument> [ , ... ] ]
COLUMNS <column> [ , <column> ]... ) [ AS ] <correlation name> [ ( <column name list> ) ]
<argument> ::= <value expression> [ AS <identifier> ] [ BY REF | BY VALUE ]
<column> ::= <column name> FOR ORDINALITY
| <column name> <data type> [ BY REF | BY VALUE ] [ DEFAULT <default option> ] [ PATH <column pattern> ]
The XQuery expression, the row pattern and each column pattern are
character string literals. The expression is parsed and analysed as the
statement is prepared, so a syntax error or a name it does not know
fails then. Every error the query raises, as it is analysed or
evaluated, is SQLSTATE 10000, XQuery error, its message naming the
error's code, such as err:XPST0008.
Arguments
An argument with no name is the context item, ., of which there is at
most one. One with a name is a variable, $name; a regular identifier is
upper case, so AS n is $N, and a delimited one, AS "n", keeps its
case. An XML value is passed BY REF, its nodes themselves, or BY VALUE,
a copy; the mechanism after PASSING applies to every argument that
writes none. Another value is cast to XML(SEQUENCE) as XMLCAST casts it,
and writes no mechanism. A null context item makes the result null; a
null variable is the empty sequence. A context item of more than one
item fails with SQLSTATE 2200V.
The XMLNAMESPACES declarations in scope, of an XMLELEMENT, XMLFOREST or
XMLTABLE around the query, or of the query, statement or definition it
is in (XML options), are the query's
namespaces too, beside xml, xs, xsi, fn, xdt, local and
sqlxml.
XMLQUERY
XMLQUERY gives the query's value. RETURNING SEQUENCE gives it as it is, of XML(SEQUENCE), BY REF or a copy BY VALUE; it needs a mechanism, its own or the one after PASSING. RETURNING CONTENT, the default, gives a document node of it, as XMLDOCUMENT does, of XML(CONTENT(UNTYPED)). NULL ON EMPTY makes the empty sequence null, EMPTY ON EMPTY keeps it.
CREATE TABLE ORDERS2 (ID INTEGER, DOC XML);
INSERT INTO ORDERS2 VALUES (1, XMLPARSE(DOCUMENT '<order><item price="10">pen</item><item price="25">ink</item></order>' STRIP WHITESPACE)), (2, XMLPARSE(DOCUMENT '<order><item price="5">cap</item></order>' STRIP WHITESPACE));
SELECT ID, XMLSERIALIZE(XMLQUERY('<total items="{count(//item)}">{//item[last()]/text()}</total>' PASSING BY REF DOC NULL ON EMPTY) AS VARCHAR(60)) FROM ORDERS2 ORDER BY ID;
SELECT ID, XMLCAST(XMLQUERY('(for $i in //item where $i/@price > $min return string($i))[last()]' PASSING BY VALUE DOC, 8 AS "min" RETURNING SEQUENCE NULL ON EMPTY) AS VARCHAR(20)) FROM ORDERS2 ORDER BY ID;
SELECT XMLQUERY('$x div 0' PASSING BY REF 1 AS "x" EMPTY ON EMPTY) FROM ORDERS2;
OK
OK
ID |
--------------+--------------------------------------------------------------
1 | <total items="2">ink</total>
2 | <total items="1">cap</total>
(2 rows)
ID |
--------------+----------------------
1 | ink
2 | NULL
(2 rows)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:FOAR0001: a divisor is zero [XQueryFO §6.2.4] [ISO/IEC 9075-14:2006 draft §6.18 GR3]
XMLEXISTS
XMLEXISTS is true if the query's value is not the empty sequence, false if it is, and unknown if the context item passed is null. It is a predicate, and a boolean value.
SELECT ID FROM ORDERS2 WHERE XMLEXISTS('//item[. = $name]' PASSING BY REF DOC, 'ink' AS "name");
SELECT ID, XMLEXISTS('/order[count(item) > 1]' PASSING BY REF DOC) FROM ORDERS2 ORDER BY ID;
ID
--------------
1
(1 row)
ID |
--------------+-------
1 | TRUE
2 | FALSE
(2 rows)
XMLTABLE
XMLTABLE is a table of FROM. It may refer to the columns of the tables
before it, as a lateral derived table does. Each item of its row
pattern's value is a row. FOR ORDINALITY numbers the rows from 1. A
regular column's pattern, PATH or the column's name, is evaluated with
the row's item as the context item. Its value is the empty sequence's
DEFAULT, else null; or it is cast to the column's type: of XML(SEQUENCE),
the value as it is, passed by the column's mechanism or the one after
PASSING; of another XML type, a document node of it, copied; of another
type, XMLCAST of a document node of it. An attribute cannot be made a
document node, so a column of an attribute's value writes data(@name),
not @name.
SELECT O.ID, I.N, I.NAME, I.PRICE FROM ORDERS2 O, XMLTABLE('/order/item' PASSING BY REF O.DOC COLUMNS N FOR ORDINALITY, NAME VARCHAR(10) PATH '.', PRICE DECIMAL(5,2) DEFAULT 0 PATH 'data(@price)') AS I ORDER BY O.ID, I.N;
SELECT * FROM XMLTABLE(XMLNAMESPACES('urn:t' AS "t"), '<t:r xmlns:t="urn:t"><t:v>1</t:v><t:v>2</t:v></t:r>/t:v' COLUMNS V INTEGER PATH '.') AS X;
ID | N | NAME | PRICE
--------------+--------------+------------+---------
1 | 1 | pen | 10.00
1 | 2 | ink | 25.00
2 | 1 | cap | 5.00
(3 rows)
V
--------------
1
2
(2 rows)
What XQuery evaluates
Every expression of XQuery 1.0 is evaluated: paths on every axis, in document order, predicates, sequences and ranges, arithmetic and comparisons, FLWOR expressions with order by, quantified, conditional and typeswitch expressions, instance of, treat, cast and castable, direct and computed constructors, ordered and unordered expressions, validate expressions (below), and a prolog's namespaces, setters, variables and functions, recursive ones too. A type error is found as the query is evaluated, unless static typing is in effect (below).
A prolog may import a registered XML Schema
(below), whose types and declarations a query then
names. A prolog may import a library module registered by CREATE XQUERY
MODULE (below) and call its functions. A library
module given as a query is refused with err:XPST0003, as a query is a
main module.
An element a query constructs has the type annotation xs:anyType, and
an element it copies keeps its own, a validated element its type; after
declare construction strip; each is xdt:untyped. An element test
such as element(*, xdt:untyped) tells them apart. XMLELEMENT and
XMLFOREST RETURNING SEQUENCE make an element of xs:anyType too. A
value of XML(CONTENT(UNTYPED)), as RETURNING CONTENT makes, has every
element xdt:untyped.
SELECT XMLCAST(XMLQUERY('<a/> instance of element(*, xdt:untyped)' NULL ON EMPTY) AS BOOLEAN), XMLCAST(XMLQUERY('declare construction strip; <a/> instance of element(*, xdt:untyped)' NULL ON EMPTY) AS BOOLEAN) FROM ORDERS2 WHERE ID = 1;
SELECT XMLCAST(XMLQUERY('./* instance of element(*, xdt:untyped)' PASSING BY REF XMLELEMENT(NAME "e") NULL ON EMPTY) AS BOOLEAN) FROM ORDERS2 WHERE ID = 1;
|
-------+-------
FALSE | TRUE
(1 row)
-------
TRUE
(1 row)
A cast to xs:QName, and its constructor function, takes a string
literal alone, as xs:QName("h:td") or "td" cast as xs:QName; any
other operand fails as the query is prepared, with err:XPST0083. The
prefix is resolved by the query's namespaces, those of its prolog and
of the XMLNAMESPACES in scope among them, a name with none by the
default element namespace; a prefix bound to none fails with
err:FONS0004.
WITH XMLNAMESPACES ('urn:shop' AS "s") SELECT XMLCAST(XMLQUERY('namespace-uri-from-QName(xs:QName("s:item"))' NULL ON EMPTY) AS VARCHAR(20)) FROM ORDERS2 WHERE ID = 1;
SELECT XMLQUERY('xs:QName(concat("s", ":item"))' NULL ON EMPTY) FROM ORDERS2 WHERE ID = 1;
----------------------
urn:shop
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XPST0083: a value cast to xs:QName, or a type derived from it or xs:NOTATION, is a string literal [XQuery §3.12.3, §3.12.5] [ISO/IEC 9075-14:2006 draft §6.18 GR3] (at character 7)
Static typing
XQuery's static typing gives each expression of a query a type before
it is evaluated, and refuses a query whose types do not fit, as XQuery
1.0 and XPath 2.0 Formal Semantics types it. It is off by default. The
setting XQUERY.STATIC_TYPING ON puts it in effect for the statements a
session, or with SET DATABASE every session, prepares; a query puts it
in effect for itself with the option static-typing of turborel's
namespace of options, urn:turborel:option. Its value off takes
nothing out of effect.
declare namespace t = "urn:turborel:option";
declare option t:static-typing "on";
In effect, every type error, err:XPTY0004 and the others XQuery names,
is raised as the statement is prepared, SQLSTATE 10000, before any row
is read; an expression whose type is empty, such as a path to an element
no type has, is err:XPST0005, and the context item read where none is
passed err:XPST0001. A value passed is of the type its SQL type gives:
an XML(DOCUMENT) or XML(CONTENT) value an optional document of elements
of its kind, of a registered XML Schema's declarations for XMLSCHEMA; an
SQL value of the XML Schema type it maps to. The typing is pessimistic:
a query refused may evaluate without error on every value, as $x/a + 1
of a document whose elements a are many in type though one in fact;
exactly-one, zero-or-one and a predicate [1] state what the types
cannot. A value passed that cannot be null, a NOT NULL column or a
literal, is one, not optional. A definition such as a view, a CHECK or a
routine is typed as it is made, as XQUERY.STATIC_TYPING of the session
making it or its query's option asks, and keeps that typing: it is read
as it was made, whatever the session reading it asks.
SET XQUERY.STATIC_TYPING = 'ON';
VALUES (XMLSERIALIZE(XMLQUERY('exactly-one(./order/qty) + 1' PASSING BY REF XMLPARSE(DOCUMENT '<order><qty>2</qty></order>' PRESERVE WHITESPACE) RETURNING SEQUENCE EMPTY ON EMPTY) AS VARCHAR(20)));
VALUES (XMLSERIALIZE(XMLQUERY('./order/qty + 1' PASSING BY REF XMLPARSE(DOCUMENT '<order><qty>2</qty></order>' PRESERVE WHITESPACE) RETURNING SEQUENCE EMPTY ON EMPTY) AS VARCHAR(20)));
VALUES (XMLSERIALIZE(XMLQUERY('./order/@qty/x' PASSING BY REF XMLPARSE(DOCUMENT '<order><qty>2</qty></order>' PRESERVE WHITESPACE) RETURNING SEQUENCE EMPTY ON EMPTY) AS VARCHAR(20)));
SET XQUERY.STATIC_TYPING TO DEFAULT;
VALUES (XMLSERIALIZE(XMLQUERY('declare namespace t = "urn:turborel:option"; declare option t:static-typing "on"; upper-case(1)' RETURNING SEQUENCE BY REF EMPTY ON EMPTY) AS VARCHAR(20)));
OK
----------------------
3
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XPTY0004: an operand of + is of (xs:double)* and xs:integer, not each of one atomic value or none [XQueryFS Appendix B.2] [ISO/IEC 9075-14:2006 draft §6.18 GR3] (at character 21)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XPST0005: the step child::x of a context item of attribute qty of type xdt:untypedAtomic selects nothing [XQuery §2.3.1] [XQueryFS §4] [ISO/IEC 9075-14:2006 draft §6.18 GR3] (at character 21)
OK
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XPTY0004: argument 1 of upper-case is of xs:integer, which cannot be promoted to (xs:string)? [XQueryFS §4.1.5, §8.5.1] [ISO/IEC 9075-14:2006 draft §6.18 GR3] (at character 21)
Functions
A query calls every function of XQuery 1.0 Functions and Operators, the
W3C Candidate Recommendation of 3 November 2005, by its name in the
namespace fn, the default, so count(//item) is fn:count(//item).
Each converts its arguments as its signature says: a node's typed value
where a value is wanted, an untyped value cast to the type wanted, an
integer promoted to a decimal or double, and the empty sequence where
? allows it.
| Kind | Functions |
|---|---|
| Accessors | node-name, nilled, string, data, base-uri, document-uri |
| Errors and tracing | error, trace |
| Constructing | dateTime, and xs:TYPE($arg) of each atomic type, which casts |
| Numbers | abs, ceiling, floor, round, round-half-to-even |
| Strings | codepoints-to-string, string-to-codepoints, compare, codepoint-equal, concat, string-join, substring, string-length, normalize-space, normalize-unicode, upper-case, lower-case, translate, encode-for-uri, iri-to-uri, escape-html-uri |
| Substrings | contains, starts-with, ends-with, substring-before, substring-after |
| Regular expressions | matches, replace, tokenize |
| URIs | resolve-uri |
| Booleans | true, false, not, boolean |
| Durations | years-from-duration, months-from-duration, days-from-duration, hours-from-duration, minutes-from-duration, seconds-from-duration |
| Dates and times | year-from-dateTime, month-from-dateTime, day-from-dateTime, hours-from-dateTime, minutes-from-dateTime, seconds-from-dateTime, timezone-from-dateTime, year-from-date, month-from-date, day-from-date, timezone-from-date, hours-from-time, minutes-from-time, seconds-from-time, timezone-from-time, adjust-dateTime-to-timezone, adjust-date-to-timezone, adjust-time-to-timezone |
| QNames | resolve-QName, QName, prefix-from-QName, local-name-from-QName, namespace-uri-from-QName, namespace-uri-for-prefix, in-scope-prefixes |
| Nodes | name, local-name, namespace-uri, number, lang, root |
| Sequences | index-of, empty, exists, distinct-values, insert-before, remove, reverse, subsequence, unordered, zero-or-one, one-or-more, exactly-one, deep-equal, count, avg, max, min, sum |
| Documents and IDs | id, idref, doc, doc-available, collection |
| The context | position, last, current-dateTime, current-date, current-time, implicit-timezone, default-collation, static-base-uri |
No document or collection is available to a query, so fn:doc of a URI
fails with err:FODC0005, fn:doc-available is false, and
fn:collection fails with err:FODC0004, or err:FODC0002 of none. The
current dateTime is the statement's, the same through it, in the
session's time zone, which is the implicit time zone. fn:error fails
with SQLSTATE 10000, its message the error's code and description;
fn:trace gives its value and writes nothing.
xs:integer and xs:decimal values have up to 1,024 digits, and an
operation past them fails with err:FOAR0002. A quotient of decimals is
exact when it ends within 18 digits after the point, or within the
scale of an operand, and is cut there otherwise. Floats and doubles
compute as IEEE 754 does. fn:normalize-unicode normalizes by Unicode
13.0, to NFC, NFD, NFKC, NFKD or FULLY-NORMALIZED. A fully normalized
string is its NFC, as XML 1.1 defines full normalization, the string
being its one construct; a string whose NFC begins with a composing
character, a combining mark or a character second in a canonical
composition, has none and fails with err:FOCH0003. fn:upper-case and fn:lower-case map each character
in full, as Unicode's tables do apart from language and context.
fn:matches, fn:replace and fn:tokenize read XQuery 1.0's regular
expressions, with the flags s, m, i and x.
SELECT XMLCAST(XMLQUERY('string-join(for $i in //item order by xs:decimal($i/@price) descending return upper-case($i), ", ")' PASSING BY REF DOC NULL ON EMPTY) AS VARCHAR(40)) FROM ORDERS2 WHERE ID = 1;
SELECT ID, XMLCAST(XMLQUERY('sum(//item/@price) div count(//item)' PASSING BY REF DOC NULL ON EMPTY) AS DECIMAL(9,2)) FROM ORDERS2 ORDER BY ID;
SELECT XMLCAST(XMLQUERY('replace("2026-11-17", "(\d+)-(\d+)-(\d+)", "$3.$2.$1")' NULL ON EMPTY) AS VARCHAR(20)), XMLCAST(XMLQUERY('tokenize("a, b,c", ",\s*")[last()]' NULL ON EMPTY) AS VARCHAR(5)) FROM ORDERS2 WHERE ID = 1;
SELECT XMLCAST(XMLQUERY('year-from-date(xs:date("2026-11-17") + xdt:yearMonthDuration("P1Y2M"))' NULL ON EMPTY) AS INTEGER), XMLCAST(XMLQUERY('days-from-duration(xs:date("2026-12-25") - xs:date("2026-11-17"))' NULL ON EMPTY) AS INTEGER) FROM ORDERS2 WHERE ID = 1;
SELECT XMLCAST(XMLQUERY('count(distinct-values(("b", "a", "B", "a")))' NULL ON EMPTY) AS INTEGER), XMLCAST(XMLQUERY('string-join(for $n in (round-half-to-even(2.5), round(2.5), abs(-3)) return string($n), " ")' NULL ON EMPTY) AS VARCHAR(20)) FROM ORDERS2 WHERE ID = 1;
SELECT XMLQUERY('exactly-one(())' EMPTY ON EMPTY) FROM ORDERS2 WHERE ID = 1;
SELECT XMLCAST(XMLQUERY('string-join(for $c in string-to-codepoints(normalize-unicode("ét", "FULLY-NORMALIZED")) return string($c), " ")' NULL ON EMPTY) AS VARCHAR(20)) FROM ORDERS2 WHERE ID = 1;
SELECT XMLQUERY('normalize-unicode("́e", "FULLY-NORMALIZED")' NULL ON EMPTY) FROM ORDERS2 WHERE ID = 1;
------------------------------------------
INK, PEN
(1 row)
ID |
--------------+-------------
1 | 17.50
2 | 5.00
(2 rows)
|
----------------------+-------
17.11.2026 | c
(1 row)
|
--------------+--------------
2028 | 38
(1 row)
|
--------------+----------------------
3 | 2 3 3
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:FORG0005: fn:exactly-one is given 0 items [XQueryFO §15.2.3] [ISO/IEC 9075-14:2006 draft §6.18 GR3]
----------------------
233 116
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:FOCH0003: a string beginning with a composing character has no fully normalized form [XQueryFO §7.4.6] [XML 1.1 Appendix B] [ISO/IEC 9075-14:2006 draft §6.18 GR3]
Strings compare by Unicode code points unless the query declares
another collation or a function is given one. A collation of the
database is the URI urn:turborel:collation: then its name, as
urn:turborel:collation:UNICODE; another fails with err:FOCH0002.
fn:contains, fn:starts-with, fn:ends-with, fn:substring-before
and fn:substring-after match by the collation's collation units. Of
the code point collation, and of one that compares one character at a
time as CASE_INSENSITIVE does, each character is a unit. Of UNICODE
and its tailorings a unit is a character and the marks after it, or a
contraction, as ch is in UNICODE_CS, so a match never splits one: a
precomposed é matches e and a combining acute accent, and c is
not found in chata by UNICODE_CS. A unit matches another of the same
weights at every level the collation compares, so UNICODE tells case
and accents apart. A match is minimal: it begins and ends at a unit of
some weight, so a character ignored at every level, as a control
character is, is left outside it.
SELECT XMLCAST(XMLQUERY('compare("pen", "PEN", "urn:turborel:collation:CASE_INSENSITIVE")' NULL ON EMPTY) AS INTEGER), XMLCAST(XMLQUERY('contains("Pen and Ink", "INK", "urn:turborel:collation:CASE_INSENSITIVE")' NULL ON EMPTY) AS BOOLEAN) FROM ORDERS2 WHERE ID = 1;
SELECT XMLCAST(XMLQUERY('contains("résumé", "és", "urn:turborel:collation:UNICODE")' NULL ON EMPTY) AS BOOLEAN), XMLCAST(XMLQUERY('declare default collation "urn:turborel:collation:UNICODE_CS"; concat(contains("chata", "c"), " ", substring-after("ochota", "ch"))' NULL ON EMPTY) AS VARCHAR(20)) FROM ORDERS2 WHERE ID = 1;
|
--------------+-------
0 | TRUE
(1 row)
|
-------+----------------------
TRUE | false ota
(1 row)
XML indexes
CREATE INDEX <index name> ON <table name> ( <XML column name> )
USING { XML_PATH | XML_VALUE | XML_PROPERTY | XML_PRIMARY }
An XML index (extension) keys the nodes of an XML column's values, so a query of a path reads the rows whose values hold it, not every row (CREATE INDEX). It keys each element and attribute below each item of a value: its path from the item, as names in namespaces; its value, the text of an element's descendants or an attribute's, as a string, by its first 64 octets, and as a number if the whole of it is one; and its place in document order. The methods order their keys for different questions, after Pal et al., "Indexing XML Data Stored in a Relational Database" (VLDB 2004):
| Method | Keyed by | Answers best |
|---|---|---|
| XML_PATH | path, value | a path, and a value at it, of any row |
| XML_VALUE | value, path | a value, at a path given in part |
| XML_PROPERTY | row, path, value | the values of paths of one row |
| XML_PRIMARY | row, place in document order; and row, path, value | a query of one large value, answered from the index alone |
XML_PRIMARY keeps each node whole: its kind, its path and its value up to
512 octets. It answers whether a row's value holds a path, a value at it
and predicates of its steps, such as /order[@no = 17]/item[qty],
without reading the value; a row whose answer needs a longer value is read and
checked. It suits large values queried for a few of their nodes; of
small values, reading the value costs less. It takes about twice the
space of the others.
Each indexes one XML column alone; another column, or more than one, is
refused (SQLCODE -103). Like every index, it is built of the rows there,
kept with each change, and changes no result. A value with more than
262,144 elements and attributes, or one of a name or path too long for
the database's page size, cannot be indexed, and its INSERT or UPDATE
fails (SQLCODE -900). Its lookups are also reached from Rust
(Database::xml_candidates).
COMMIT;
CREATE INDEX ORDER_PATHS ON ORDERS (BODY) USING XML_PATH;
CREATE INDEX ORDER_VALUES ON ORDERS (BODY) USING XML_VALUE;
CREATE INDEX ORDER_ROWS ON ORDERS (BODY) USING XML_PRIMARY;
CREATE INDEX ORDER_IDS ON ORDERS (ID) USING XML_PROPERTY;
DROP INDEX ORDER_VALUES;
SELECT INDEX_NAME, METHOD_NAME, COLUMN_NAMES FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'ORDERS' ORDER BY INDEX_NAME;
OK
OK
OK
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): index ORDER_IDS of method xml_property keys one column, of an XML type (RULES.md D106)
OK
INDEX_NAME | METHOD_NAME | COLUMN_NAMES
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
ORDER_PATHS | xml_path | BODY
ORDER_ROWS | xml_primary | BODY
(2 rows)
Queries an XML index reads for
The planner reads an XML index for a condition on a table's rows that keeps a row only if a query's value of the row's column is not empty:
- XMLEXISTS, in WHERE or in a join's ON;
- XMLQUERY ... NULL ON EMPTY IS NOT NULL;
- XMLTABLE joined to the rows it is of, not by a LEFT join: a row its row pattern gives nothing of gives no row.
The query's context item is the column, of any XML type, passed BY REF
or BY VALUE, and nothing else is passed. Its
body is one path from . or / of child, descendant (//) and
attribute steps over names or *, among which self of a name and
parent (..) of a child or attribute step may stand, and as its last
step ancestor, ancestor-or-self, following, following-sibling,
preceding or preceding-sibling of a name, or a kind test such as
text(). Any step may have predicates, each a
relative path of such steps, or a comparison of one, or of . of the
last step, with a literal, or several of these joined by and, as in
/order[@no = "17"][customer]/item[qty = "2" and name]. A comparison by
=, !=, <, <=, > or >= with a string or a number narrows the
rows the index gives; any other leaves its path alone. The index reads
the path, each predicate's path from the start, and, of a last step of
another axis, the path of its elements, and gives the rows each of them
gives; of a predicate compared with a number, which may
fail of a node it cannot cast, the rows its own path gives too. Several
such conditions of one column joined by AND are read alike. A query of
another shape, or a table too small to gain, is read whole.
The index gives the rows whose values may hold such a node. Each is fetched and the condition checked on it, so the result is a full read's. A row whose value a numeric comparison cannot cast is among those it gives, so its error is raised as by a full read. If another condition of the table may raise an error, the table is read whole, so the error is raised whatever rows the index would skip. In ON, a query that may raise an error, as a numeric comparison may, is still evaluated on every row of its table while the other table has rows, and on none while it has none: the index gives its rows only then, as a WHERE clause's would, and the query checks each.
A value of XML(SEQUENCE) may be no one document node: several items, an atomic value, an element or none. A query of it may fail, as of several items (SQLSTATE 2200V), or start from no document, so the index gives the row of every such value, whatever the path; each is checked, and its error raised as by a full read.
XML_PRIMARY answers of each row from the index alone a query of a path,
its comparisons with strings and its steps' predicates, where each
predicate's node is fixed of a node the path selects, as it is but of a
predicate between two // steps, and a last step of another axis, by
comparing the places in document order its index keeps: a row it
holds of is not checked, only one whose answer needs a value longer
than it keeps whole. Of the other methods, of a query compared with a
number or by a value comparison, and of a column of XML(SEQUENCE), each
row given is checked. The
planner weighs reading an index against reading every row and checking
its value, by the values' average length. EXPLAIN shows the index read
by its method, the path, predicate and comparison it looks up, and
whether it answers the query.
CREATE TABLE PARCELS (ID INTEGER, LABEL XML(DOCUMENT));
INSERT INTO PARCELS WITH RECURSIVE N (I) AS (SELECT CAST(1 AS INTEGER) FROM ORDERS WHERE ID = 1 UNION ALL SELECT I + 1 FROM N WHERE I < 400) SELECT I, XMLPARSE(DOCUMENT '<parcel><to>' || CAST(I AS VARCHAR(3)) || '</to>' || CASE WHEN I > 395 THEN '<fragile/>' ELSE '' END || '</parcel>' STRIP WHITESPACE) FROM N;
COMMIT;
CREATE INDEX PARCEL_PATHS ON PARCELS (LABEL) USING XML_PATH;
SELECT ID FROM PARCELS WHERE XMLEXISTS('/parcel[to = "7"]' PASSING BY REF LABEL) ORDER BY ID;
EXPLAIN SELECT ID FROM PARCELS WHERE XMLEXISTS('/parcel[to = "7"]' PASSING BY REF LABEL);
EXPLAIN SELECT P.ID FROM PARCELS P, XMLTABLE('//fragile' PASSING BY REF P.LABEL COLUMNS F FOR ORDINALITY) AS X;
OK
OK
OK
OK
ID
--------------
7
(1 row)
PLAN
--------------------------------------------------------------
-- bound:
Project PARCELS.ID
Filter XMLEXISTS(PARCELS.LABEL)
Scan ANN.PARCELS
-- rules: none
-- optimized:
Project PARCELS.ID
Filter XMLEXISTS(PARCELS.LABEL)
Scan ANN.PARCELS
-- physical:
Project PARCELS.ID
Filter XMLEXISTS(PARCELS.LABEL) (est. rows=0)
IndexScan ANN.PARCELS using PARCEL_PATHS [xml_path]: PARCELS.LABEL has /parcel/to = '7' (est. rows=1, cost=60.5)
(13 rows)
PLAN
--------------------------------------------------------------
-- bound:
Project P.ID
Unnest XMLTABLE(P.LABEL)
Scan ANN.PARCELS
-- rules: none
-- optimized:
Project P.ID
Unnest XMLTABLE(P.LABEL)
Scan ANN.PARCELS
-- physical:
Project P.ID
Unnest of 1 collections
IndexScan ANN.PARCELS using PARCEL_PATHS [xml_path]: P.LABEL has //fragile (est. rows=10, cost=137.9)
(13 rows)
COMMIT;
DROP INDEX PARCEL_PATHS;
CREATE INDEX PARCEL_NODES ON PARCELS (LABEL) USING XML_PRIMARY;
SELECT ID FROM PARCELS WHERE XMLEXISTS('/parcel[to = "398"]/fragile' PASSING BY REF LABEL);
EXPLAIN SELECT ID FROM PARCELS WHERE XMLEXISTS('/parcel[to = "398"]/fragile' PASSING BY REF LABEL);
OK
OK
OK
ID
--------------
398
(1 row)
PLAN
--------------------------------------------------------------
-- bound:
Project PARCELS.ID
Filter XMLEXISTS(PARCELS.LABEL)
Scan ANN.PARCELS
-- rules: none
-- optimized:
Project PARCELS.ID
Filter XMLEXISTS(PARCELS.LABEL)
Scan ANN.PARCELS
-- physical:
Project PARCELS.ID
IndexScan ANN.PARCELS using PARCEL_NODES [xml_primary]: PARCELS.LABEL has /parcel[to = '398']/fragile, answered by the index (est. rows=6, cost=103.5)
(12 rows)
A parent step is a predicate of the step before it, which the primary answers; a predicate compared with a number is read by its own lookup, each row it gives checked:
SELECT ID FROM PARCELS WHERE XMLEXISTS('/parcel/fragile/../to[. = "397"]' PASSING BY REF LABEL);
EXPLAIN SELECT ID FROM PARCELS WHERE XMLEXISTS('/parcel/fragile/../to[. = "397"]' PASSING BY REF LABEL);
SELECT COUNT(*) FROM PARCELS WHERE XMLEXISTS('/parcel[to > 398][fragile]' PASSING BY REF LABEL);
ID
--------------
397
(1 row)
PLAN
--------------------------------------------------------------
-- bound:
Project PARCELS.ID
Filter XMLEXISTS(PARCELS.LABEL)
Scan ANN.PARCELS
-- rules: none
-- optimized:
Project PARCELS.ID
Filter XMLEXISTS(PARCELS.LABEL)
Scan ANN.PARCELS
-- physical:
Project PARCELS.ID
IndexScan ANN.PARCELS using PARCEL_NODES [xml_primary]: PARCELS.LABEL has /parcel[fragile]/to = '397', answered by the index (est. rows=2, cost=69.1)
(12 rows)
----------------------
2
(1 row)
XML options of a scope
<XML lexically scoped options> ::= <XML lexically scoped option> [ , <XML lexically scoped option> ]
<XML lexically scoped option> ::=
XMLNAMESPACES ( <XML namespace declaration item> [ , ... ] )
| XMLBINARY [ USING ] { BASE64 | HEX }
<with clause> ::= WITH [ <XML lexically scoped options> ] [ , ] [ [ RECURSIVE ] <with list> ]
DELETE [ WITH <XML lexically scoped options> ] FROM ...
INSERT [ WITH <XML lexically scoped options> ] INTO ...
MERGE [ WITH <XML lexically scoped options> ] INTO ...
UPDATE [ WITH <XML lexically scoped options> ] <table name> SET ...
CHECK ( [ WITH <XML lexically scoped options> ] <search condition> )
GENERATED ALWAYS AS ( [ WITH <XML lexically scoped options> ] <value expression> )
BEGIN [ [ NOT ] ATOMIC ] [ DECLARE <XML lexically scoped options> ; ] ... END
XMLNAMESPACES and XMLBINARY may be declared for a whole query, a whole statement or a whole definition, as well as in one XMLELEMENT or XMLFOREST. Each holds at most one XMLNAMESPACES and one XMLBINARY.
XMLNAMESPACES names the elements and attributes of XMLELEMENT and XMLFOREST, and is among the namespaces of XMLQUERY's, XMLEXISTS's and XMLTABLE's queries.
XMLBINARY says how XMLELEMENT, XMLFOREST, XMLCAST and XMLMAP of no ENCODING write a binary string, and how XMLCAST reads one: BASE64 in base64, HEX in hex. Where no XMLBINARY is in scope it is HEX. XMLPI always writes hex.
The options after WITH, before a query's WITH list or alone, are in scope of the whole query: its subqueries, derived tables, the queries of its WITH list and the operands of its UNION, EXCEPT and INTERSECT. A comma comes between the options and the WITH list, and only there. After the first word of DELETE, INSERT, MERGE or UPDATE, positioned or searched, they are in scope of the whole statement. A CHECK, of a table, a domain or an assertion, and a generation expression are in their scope, and keep them with their text, so they mean the same whenever the definition is read again. DECLARE as a compound statement begins puts them in scope of all it holds. The innermost declaration of a name wins: an XMLELEMENT's own, then each query's from the inside out, then the statement's or the definition's, then the compound statements'.
A view keeps the options of its query, and a routine those of its body; neither sees those of the statement that reads or invokes it.
WITH XMLBINARY USING BASE64 SELECT XMLSERIALIZE(XMLFOREST(ID, PHOTO) AS VARCHAR(60)) FROM STAFF WHERE ID = 1;
WITH XMLNAMESPACES('http://www.w3.org/1999/xhtml' AS "h"), XMLBINARY BASE64 SELECT XMLSERIALIZE(XMLELEMENT(NAME "h:img", XMLATTRIBUTES(PHOTO AS "h:src"), (SELECT XMLELEMENT(NAME "h:b", NAME) FROM STAFF WHERE ID = 2)) AS VARCHAR(200)) FROM STAFF WHERE ID = 1;
CREATE TABLE PICTURES (ID INTEGER, PHOTO VARBINARY(4), TAG VARCHAR(40) GENERATED ALWAYS AS (WITH XMLBINARY BASE64 XMLSERIALIZE(XMLELEMENT(NAME "img", PHOTO) AS VARCHAR(40))));
INSERT WITH XMLBINARY BASE64 INTO PICTURES (ID, PHOTO) VALUES (1, XMLCAST(XMLPARSE(CONTENT 'D7c=' PRESERVE WHITESPACE) AS VARBINARY(4)));
SELECT ID, PHOTO, TAG FROM PICTURES;
WITH XMLBINARY BASE64, XMLBINARY HEX SELECT ID FROM STAFF;
--------------------------------------------------------------
<ID>1</ID><PHOTO>D7c=</PHOTO>
(1 row)
--------------------------------------------------------------
<h:img xmlns:h="http://www.w3.org/1999/xhtml" h:src="D7c="><h:b>Bo & Co</h:b></h:img>
(1 row)
OK
OK
ID | PHOTO | TAG
--------------+----------+------------------------------------------
1 | 0FB7 | <img>D7c=</img>
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): the options declare namespaces once and a binary encoding once [ISO/IEC 9075-14:2006 draft §11.3 SR1] (at character 23)
Mapping names
XMLNAME_TO_IDENTIFIER ( <character value expression> )
IDENTIFIER_TO_XMLNAME ( <character value expression> [ , { PARTIALLY | FULLY } ESCAPED ] )
(extension) An SQL identifier may hold characters an XML Name may not, so SQL/XML maps identifiers to XML Names, as XMLELEMENT, XMLFOREST and XMLMAP map column names, and XML Names back to identifiers. These two functions apply the mappings to a string.
IDENTIFIER_TO_XMLNAME maps the characters of an identifier to an XML
Name. A character no XML 1.1 name holds, or a first character no name
begins with, is written _xHHHH_ in hexadecimal, or _xHHHHHH_ beyond
the Basic Multilingual Plane, and an underscore before a lowercase x
is written _x005F_. PARTIALLY ESCAPED, the default, keeps a colon
after the first character; FULLY ESCAPED escapes every colon and the
first letter of a leading xml in any case.
XMLNAME_TO_IDENTIFIER maps an XML Name back: each _xHHHH_ or
_xHHHHHH_ of an assigned Unicode character becomes that character,
any other stays as written. It gives the delimited identifier, in
double quotes, each double quote doubled. Either mapping read back
gives the identifier again.
Each takes a character string, not a large object, and gives a CHARACTER VARYING string: of XMLNAME_TO_IDENTIFIER twice the argument's length and 2, of IDENTIFIER_TO_XMLNAME 9 times it, at most 32,767 characters. A longer result fails with SQLSTATE 22001. A null argument gives null. The words are no key words.
VALUES (IDENTIFIER_TO_XMLNAME('order line'), IDENTIFIER_TO_XMLNAME('ns:a', FULLY ESCAPED), IDENTIFIER_TO_XMLNAME('xml1', FULLY ESCAPED));
VALUES (XMLNAME_TO_IDENTIFIER('order_x0020_line'), XMLNAME_TO_IDENTIFIER('say_x0020__x0022_hi_x0022_'));
| |
--------------------------------------------------------------+--------------------------------------+--------------------------------------
order_x0020_line | ns_x003A_a | _x0078_ml1
(1 row)
|
------------------------------------+--------------------------------------------------------
"order line" | "say ""hi"""
(1 row)
Mapping tables to XML
XMLMAP (extension) maps a table, a schema or the catalog to XML, and to an XML Schema document that describes that XML, as SQL/XML defines the mappings. SQL/XML gives the mappings no syntax, so XMLMAP is turborel's own. Its clauses are written in this order, each at most once.
XMLMAP ( { TABLE <table name> | SCHEMA <schema name> | CATALOG }
[ NULLS { ABSENT | NIL } ]
[ AS { FOREST | ELEMENT } ]
[ TARGETNAMESPACE <character string literal> ]
[ DATA | METADATA | DATA AND METADATA ]
[ ENCODING { BASE64 | HEX } ] )
DATA, the default, gives the data. A table is an element named after
it, holding an element row for each row, which holds an element for
each column named after the column. A value is written as XMLFOREST
writes it: a string's characters escaped, a number, a datetime, an
interval or a truth value as XML Schema writes it, an XML value as its
serialization. A schema is an element named after it, holding the
element of each of its tables, and the catalog an element named after
it (the setting CATALOG.NAME), holding the element of each schema.
Names are escaped as XMLELEMENT's are, a character a name cannot hold
written _xHHHH_. The rows are in the order the table is read, the
tables and schemas in order of name.
NULLS ABSENT, the default, leaves a null value's element out. NULLS NIL
writes it empty, with the attribute xsi:nil="true". AS FOREST maps a
table to an element named after the table for each row, with no element
around them; AS ELEMENT, the default, maps it as above. TARGETNAMESPACE
puts the elements in a namespace; with none, or with '', they are in
none. ENCODING writes binary strings in HEX or BASE64; with none
written, they are in the XMLBINARY in scope, HEX outside every one.
METADATA gives the XML Schema document instead. It defines a type of each column's data type, distinct type or domain, named after it, a type of each table's rows, and, AS ELEMENT, of each table; then of the schema and of the catalog, and declares the element at the root of the data. DATA AND METADATA gives both: the schema document, then the data.
Only what the user may read is mapped. TABLE needs SELECT on the table
(SQLCODE -104). A schema or the catalog holds only the tables the user
may SELECT that have a column to map, and the catalog only the schemas
of such tables, the Information Schema among them. A column is left out
if it is of XML(SEQUENCE) (XML alone), a structured or reference type,
DECFLOAT or a plug-in's type, or of a row, array, multiset or distinct
type made of one, or of a distinct type whose cast to its source type
the user may not execute. A row is an element of each field, a null one
left out or nilled as NULLS says, and an array or a multiset an element
element of each element, a null one nilled. A mapping
that leaves out a column the user may read, as its type does not map,
completes with the warning 01010 (column cannot be mapped).
| Mapping | Value |
|---|---|
| DATA, AS ELEMENT, or of a schema or the catalog | XML(DOCUMENT(UNTYPED)): a document |
| DATA of a table AS FOREST | XML(CONTENT(UNTYPED)): the rows' elements |
| METADATA | XML(DOCUMENT(UNTYPED)): the XML Schema document |
| DATA AND METADATA | XML(SEQUENCE): the schema document, then the data |
A view whose query maps a table depends on the table, so DROP TABLE
RESTRICT is refused while it does. A view, a routine or a trigger whose
text maps a schema depends on the schema: DROP SCHEMA RESTRICT is
refused while it is there, and CASCADE drops it. A mapping of a schema
or of the catalog reads the tables there are as it runs, so a table
added later is mapped, and one dropped is not; a mapping of the catalog
depends on nothing that can be dropped. As a view that maps a schema
could be in the schema it maps, a mapping of a schema or the catalog
leaves out a view whose query maps a schema or the catalog, or reads a
view that does; one that meets such a mapping through a routine is
refused (SQLCODE -103). A CHECK constraint may not hold
XMLMAP (SQLCODE -103), as it reads rows in an order not determined. A
target namespace of the namespace of xml or xmlns is refused
(SQLCODE -103), and a schema that is not there is SQLSTATE 3F000.
VALUES (XMLSERIALIZE(CONTENT XMLMAP(TABLE STAFF) AS VARCHAR(400)));
VALUES (XMLSERIALIZE(CONTENT XMLMAP(TABLE STAFF NULLS NIL AS FOREST ENCODING BASE64) AS VARCHAR(400)));
WITH XMLBINARY USING BASE64 SELECT XMLSERIALIZE(CONTENT XMLMAP(TABLE STAFF AS FOREST) AS VARCHAR(400)) FROM STAFF WHERE ID = 1;
CREATE SCHEMA SHOP CREATE TABLE ITEMS (SKU CHAR(4) NOT NULL, PRICE NUMERIC(6,2));
INSERT INTO SHOP.ITEMS VALUES ('A100', 9.99), ('B200', NULL);
VALUES (XMLSERIALIZE(CONTENT XMLMAP(SCHEMA SHOP TARGETNAMESPACE 'urn:shop') AS VARCHAR(400)));
VALUES (XMLSERIALIZE(CONTENT XMLMAP(SCHEMA SHOP METADATA) AS VARCHAR(2000)));
VALUES (XMLMAP(TABLE NOWHERE));
--------------------------------------------------------------
<STAFF xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><row><ID>1</ID><NAME>Ann</NAME><PAY>1200.50</PAY><PHOTO>0FB7</PHOTO></row><row><ID>2</ID><NAME>Bo & Co</NAME></row></STAFF>
(1 row)
--------------------------------------------------------------
<STAFF xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><ID>1</ID><NAME>Ann</NAME><PAY>1200.50</PAY><PHOTO>D7c=</PHOTO></STAFF><STAFF xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><ID>2</ID><NAME>Bo & Co</NAME><PAY xsi:nil="true"/><PHOTO xsi:nil="true"/></STAFF>
(1 row)
--------------------------------------------------------------
<STAFF><ID>1</ID><NAME>Ann</NAME><PAY>1200.50</PAY><PHOTO>D7c=</PHOTO></STAFF><STAFF><ID>2</ID><NAME>Bo & Co</NAME></STAFF>
(1 row)
OK
OK
--------------------------------------------------------------
<SHOP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:shop"><ITEMS><row><SKU>A100</SKU><PRICE>9.99</PRICE></row><row><SKU>B200</SKU></row></ITEMS></SHOP>
(1 row)
--------------------------------------------------------------
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"><xs:simpleType name="CHAR_4"><xs:restriction base="xs:string"><xs:length value="4"/></xs:restriction></xs:simpleType><xs:simpleType name="NUMERIC_6_2"><xs:restriction base="xs:decimal"><xs:totalDigits value="6"/><xs:fractionDigits value="2"/></xs:restriction></xs:simpleType><xs:complexType name="RowType.TURBOREL.SHOP.ITEMS"><xs:sequence><xs:element name="SKU" type="CHAR_4"/><xs:element name="PRICE" type="NUMERIC_6_2" minOccurs="0"/></xs:sequence></xs:complexType><xs:complexType name="TableType.TURBOREL.SHOP.ITEMS"><xs:sequence><xs:element name="row" type="RowType.TURBOREL.SHOP.ITEMS" minOccurs="0" maxOccurs="unbounded"/></xs:sequence></xs:complexType><xs:complexType name="SchemaType.TURBOREL.SHOP"><xs:all><xs:element name="ITEMS" type="TableType.TURBOREL.SHOP.ITEMS"/></xs:all></xs:complexType><xs:element name="SHOP" type="SchemaType.TURBOREL.SHOP"/></xs:schema>
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): table ANN.NOWHERE is not defined [X3.135-1986 §5.4 SR5] (at character 21)
Registered XML Schemas
An XML Schema is registered by CREATE XML SCHEMA from one schema document, under a name in an SQL schema, and dropped by DROP XML SCHEMA. Its document may include, redefine or import the documents of other registered XML Schemas its schema's owner may use, and nothing outside the database. The XML Schemas of the namespaces of XML Schema, of XML Schema instances and of SQL/XML are registered always, as INFORMATION_SCHEMA.XS, XSI and SQLXML. XMLVALIDATE and IS VALID validate values by one, and the XMLSCHEMA types hold the values it validates.
CREATE XML SCHEMA NOTES AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:notes"><xs:complexType name="text"/></xs:schema>';
CREATE XML SCHEMA PAGES AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:n="urn:notes" targetNamespace="urn:pages"><xs:import namespace="urn:notes"/><xs:element name="page" type="n:text"/></xs:schema>';
DROP XML SCHEMA NOTES RESTRICT;
OK
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): registered XML Schema ANN.PAGES depends on registered XML Schema ANN.NOTES, so it is dropped only with CASCADE (at character 16)
Every user may use the XML Schemas registered always. The owner of a registered XML Schema's schema may use it, and grants USAGE on it to others:
GRANT USAGE ON XML SCHEMA <registered XML Schema name> TO <grantee> [ { , <grantee> }... ] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ] USAGE ON XML SCHEMA <registered XML Schema name> FROM <grantee> [ { , <grantee> }... ] { CASCADE | RESTRICT }
See GRANT and REVOKE. INFORMATION_SCHEMA.XML_SCHEMAS, XML_SCHEMA_NAMESPACES and XML_SCHEMA_ELEMENTS describe those a user may use (The Information Schema):
SELECT XML_SCHEMA_NAME, XML_SCHEMA_TARGET_NAMESPACE, XML_SCHEMA_IS_DETERMINISTIC FROM INFORMATION_SCHEMA.XML_SCHEMAS ORDER BY 1;
SELECT XML_SCHEMA_ELEMENT, XML_SCHEMA_ELEMENT_IS_DETERMINISTIC FROM INFORMATION_SCHEMA.XML_SCHEMA_ELEMENTS WHERE XML_SCHEMA_NAME = 'SQLXML' ORDER BY 1;
GRANT USAGE ON XML SCHEMA INFORMATION_SCHEMA.XS TO BOB;
GRANT USAGE ON XML SCHEMA NOTES TO BOB;
XML_SCHEMA_NAME | XML_SCHEMA_TARGET_NAMESPACE | XML_SCHEMA_IS_DETERMINISTIC
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
NOTES | urn:notes | YES
PAGES | urn:pages | YES
SQLXML | http://standards.iso.org/iso/9075/2003/sqlxml | YES
XS | http://www.w3.org/2001/XMLSchema | NO
XSI | http://www.w3.org/2001/XMLSchema-instance | YES
(5 rows)
XML_SCHEMA_ELEMENT | XML_SCHEMA_ELEMENT_IS_DETERMINISTIC
--------------------------------------------------------------+--------------------------------------------------------------
sqlname | YES
sqltype | YES
(2 rows)
OK, with a warning: SQLSTATE 01007
OK
XS is not deterministic, as each of its elements may hold an annotation whose content is any element at all. Every user holds USAGE on XS already, without grant option, so the first grant gives BOB nothing more; the second lets BOB's own registered XML Schemas import NOTES.
XML_SCHEMAS_S, XML_SCH_NSPACES_S and XML_SCH_ELEMENTS_S show the same rows as the three views, under SQL/XML's short names (The short name views):
SELECT XML_SCHEMA_NAME, XML_SCH_TGT_NSPACE, XML_SCHEMA_IS_PERM FROM INFORMATION_SCHEMA.XML_SCHEMAS_S WHERE XML_SCHEMA_SCHEMA = 'ANN' ORDER BY 1;
SELECT XML_SCHEMA_NAME, XML_SCH_NSPACE, XML_SCH_NSP_IS_DET FROM INFORMATION_SCHEMA.XML_SCH_NSPACES_S WHERE XML_SCHEMA_NAME = 'PAGES' ORDER BY 2;
XML_SCHEMA_NAME | XML_SCH_TGT_NSPACE | XML_SCHEMA_IS_PERM
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
NOTES | urn:notes | NO
PAGES | urn:pages | NO
(2 rows)
XML_SCHEMA_NAME | XML_SCH_NSPACE | XML_SCH_NSP_IS_DET
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
PAGES | urn:notes | YES
PAGES | urn:pages | YES
(2 rows)
PAGES's namespaces are its target namespace and urn:notes, which it imports.
The XMLSCHEMA types
<secondary XML type modifier> ::=
XMLSCHEMA <XML valid according to what> [ <XML valid element clause> ]
<XML valid according to what> ::=
URI <XML URI> [ LOCATION <XML URI> ]
| NO NAMESPACE [ LOCATION <XML URI> ]
| ID <registered XML Schema name>
<XML valid element clause> ::=
ELEMENT <identifier>
| { NO NAMESPACE | NAMESPACE <XML URI> } [ ELEMENT <identifier> ]
<XML URI> ::= <character string literal>
XML(DOCUMENT(XMLSCHEMA ...)) and XML(CONTENT(XMLSCHEMA ...)) hold
values valid by a registered XML Schema. ID names it. URI names it by its
target namespace, NO NAMESPACE by having none, and LOCATION by its
schema location too: of those its user may use, the one of the least
name is taken. The element clause narrows the type to elements of a
namespace of the schema, or to one global element declaration, whose
name is the identifier's text as written, so ELEMENT "book" names the
element book. ELEMENT alone takes the schema's target namespace. A
namespace the schema has not is refused with SQLSTATE 2201M, an element
it does not declare with 2201N, as the type is written.
A column of such a type takes a value of a type of XMLSCHEMA of the same registered XML Schema, of the namespace and the element the column names, if it names them; anything else is refused with SQLCODE -103. Such a value is made by XMLVALIDATE, or by CAST BY VALUE, which validates as XMLVALIDATE does. A value sent by a client as a parameter of such a type is refused with SQLSTATE 2201R, as no validation made it. A column, a domain and a distinct type of such a type keep its registered XML Schema from being dropped, even with CASCADE; a routine with a parameter or result of one is dropped with it by DROP XML SCHEMA CASCADE.
The elements and attributes of a validated value are typed: XQuery reads
an xs:integer element as an integer, and XMLCAST to an SQL type casts
that value. Values of types of XMLSCHEMA of one registered XML Schema
brought together by UNION or CASE are of a type of it that names what
all name; with any other type, XML(CONTENT(ANY)). A type of XMLSCHEMA is
written with ID and the registered XML Schema's full name.
CREATE XML SCHEMA BOOKS AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:books" elementFormDefault="qualified">'
'<xs:element name="book"><xs:complexType><xs:sequence>'
'<xs:element name="title" type="xs:string"/><xs:element name="pages" type="xs:integer"/>'
'</xs:sequence></xs:complexType></xs:element></xs:schema>';
CREATE TABLE SHELF (ID INTEGER, BOOK XML(DOCUMENT(XMLSCHEMA URI 'urn:books' ELEMENT "book")));
INSERT INTO SHELF VALUES (1, XMLVALIDATE(DOCUMENT XMLPARSE(DOCUMENT '<book xmlns="urn:books"><title>Dune</title><pages>412</pages></book>' PRESERVE WHITESPACE) ACCORDING TO XMLSCHEMA ID BOOKS ELEMENT "book"));
INSERT INTO SHELF VALUES (2, CAST(XMLPARSE(DOCUMENT '<book xmlns="urn:books"><title>Emma</title><pages>474</pages></book>' PRESERVE WHITESPACE) AS XML(DOCUMENT(XMLSCHEMA ID BOOKS ELEMENT "book")) BY VALUE));
INSERT INTO SHELF VALUES (3, XMLPARSE(DOCUMENT '<book xmlns="urn:books"><title>Kim</title><pages>368</pages></book>' PRESERVE WHITESPACE));
SELECT ID, XMLCAST(XMLQUERY('declare default element namespace "urn:books"; data(/book/pages) instance of xs:integer' PASSING BY REF BOOK RETURNING SEQUENCE EMPTY ON EMPTY) AS BOOLEAN) AS TYPED, XMLCAST(XMLQUERY('declare default element namespace "urn:books"; /book/pages' PASSING BY REF BOOK RETURNING SEQUENCE EMPTY ON EMPTY) AS INTEGER) + 1 AS MORE FROM SHELF ORDER BY ID;
DROP XML SCHEMA BOOKS CASCADE;
OK
OK
OK
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): cannot assign a XML(DOCUMENT(UNTYPED)) value to column BOOK (XML(DOCUMENT(XMLSCHEMA ID ANN.BOOKS NAMESPACE 'urn:books' ELEMENT "book"))) [ISO/IEC 9075:1992 §13.8 SR6; ISO/IEC 9075-14:2006 draft §10.2 SR1b] (at character 29)
ID | TYPED | MORE
--------------+-------+----------------------
1 | TRUE | 413
2 | TRUE | 475
(2 rows)
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.SHELF is of an XML type of registered XML Schema ANN.BOOKS, so it is not dropped, even with CASCADE (at character 16)
XMLVALIDATE
XMLVALIDATE ( { DOCUMENT | CONTENT | SEQUENCE } <XML value expression>
[ ACCORDING TO XMLSCHEMA <XML valid according to what>
[ <XML valid element clause> ] ] )
XMLVALIDATE validates an XML value by a registered XML Schema, as
XQuery's validate strict does, and gives a copy of it whose elements
and attributes carry their types, with the default values the schema
gives added. DOCUMENT takes a document and validates it whole, its IDs
and identity constraints checked. CONTENT takes a document node and
validates each element child of it alone; SEQUENCE takes document,
element, comment and processing instruction nodes, and validates each
element, and each element child of a document node, alone. Comments and
processing instructions are copied. The null value gives the null value.
ACCORDING TO names the registered XML Schema, as a type of XMLSCHEMA does, and its user must be able to use it. Its element clause makes each element validated be of that namespace or that name. Without it, each element is validated by the registered XML Schema of its namespace that the user may use, the one of the least name if there are several; an element of no namespace by one of no target namespace. The registered XML Schemas INFORMATION_SCHEMA.XS, XSI and SQLXML validate as the texts define them: XS by the Schema for Schemas, so a schema document is valid by it; SQLXML by SQL/XML's own XML Schema; XSI declares no element.
Of a DOCUMENT or CONTENT value with ACCORDING TO, the result is of XML(DOCUMENT(XMLSCHEMA ...)) or XML(CONTENT(XMLSCHEMA ...)) of what the clause names; without, of XML(DOCUMENT(ANY)) or XML(CONTENT(ANY)). Of XML(SEQUENCE), the result is of XML(SEQUENCE).
A view, a CHECK, a domain's constraint, an assertion, a trigger or a routine that names a registered XML Schema, by XMLVALIDATE, IS VALID or a type of XMLSCHEMA, takes the one it named when it was created, even if another of the same target namespace and a lesser name is registered later, and depends on it: DROP XML SCHEMA RESTRICT is refused, and CASCADE drops it too.
| SQLSTATE | When |
|---|---|
| 2200M | DOCUMENT of a value that is no document |
| 2200N | CONTENT of a value that is not one document node |
| 2201J | SEQUENCE of an atomic value, an attribute or a text node |
| 2201K | A document node validated has a text child |
| 2201L | No registered XML Schema the user may use is of an element's namespace |
| 2201M | The element clause names a namespace the schema has not |
| 2201N | The element clause names an element the schema does not declare |
| 2201P | An element is not of the name the element clause gives |
| 2201Q | An element is not of the namespace the element clause gives |
| 2201R | An element is not valid |
CREATE XML SCHEMA SIZES AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"><xs:element name="size" type="xs:positiveInteger"/></xs:schema>';
VALUES (XMLSERIALIZE(CONTENT XMLVALIDATE(CONTENT XMLPARSE(CONTENT '<size>12</size><!--and--><size>4</size>' PRESERVE WHITESPACE)) AS VARCHAR(60)));
VALUES (XMLVALIDATE(CONTENT XMLPARSE(CONTENT '<size>0</size>' PRESERVE WHITESPACE) ACCORDING TO XMLSCHEMA ID SIZES));
VALUES (XMLVALIDATE(CONTENT XMLPARSE(CONTENT '<size>3</size>' PRESERVE WHITESPACE) ACCORDING TO XMLSCHEMA NO NAMESPACE ELEMENT "width"));
VALUES (XMLVALIDATE(DOCUMENT XMLPARSE(DOCUMENT '<weight xmlns="urn:weights">3</weight>' PRESERVE WHITESPACE)));
OK
--------------------------------------------------------------
<size>12</size><!--and--><size>4</size>
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2201R): validation failure: [Schema1 §3.3.4 cvc-type.3.1.3] '0' is no value of the element's type [ISO/IEC 9075-14:2006 draft §6.20 GR6b iv]
turborel: SQLCODE -301 (SQLSTATE 2201N): registered XML Schema ANN.SIZES declares no global element width of namespace '' [ISO/IEC 9075-14:2006 draft §11.6 GR1b] (at character 106)
turborel: SQLCODE -301 (SQLSTATE 2201L): no registered XML Schema of namespace 'urn:weights' may be used: no XML schema found [ISO/IEC 9075-14:2006 draft §6.20 GR6b i 2]
IS VALID
<XML valid predicate> ::= <XML value expression> IS [ NOT ] VALID
{ DOCUMENT | CONTENT | SEQUENCE }
[ ACCORDING TO XMLSCHEMA <XML valid according to what>
[ <XML valid element clause> ] ]
IS VALID is True if XMLVALIDATE of the value would succeed, and raises nothing: it is False of a value not of the shape DOCUMENT, CONTENT or SEQUENCE asks, or of an element not valid, or not of the element clause's name or namespace. Without ACCORDING TO, an element of a namespace of no registered XML Schema the user may use makes it Unknown, unless another makes it False. It is Unknown of the null value. A CHECK of IS VALID keeps a column of an UNTYPED type valid by a schema.
CREATE TABLE SIZED (S XML(CONTENT(UNTYPED)) CHECK (S IS VALID CONTENT ACCORDING TO XMLSCHEMA ID SIZES));
INSERT INTO SIZED VALUES (XMLPARSE(CONTENT '<size>8</size>' PRESERVE WHITESPACE));
INSERT INTO SIZED VALUES (XMLPARSE(CONTENT '<size>-8</size>' PRESERVE WHITESPACE));
SELECT CASE WHEN X IS VALID CONTENT THEN 'valid' WHEN X IS NOT VALID CONTENT THEN 'not valid' ELSE 'unknown' END AS IT FROM (VALUES (XMLPARSE(CONTENT '<size>8</size>' PRESERVE WHITESPACE)), (XMLPARSE(CONTENT '<size>eight</size>' PRESERVE WHITESPACE)), (XMLPARSE(CONTENT '<w xmlns="urn:none"/>' PRESERVE WHITESPACE))) AS V (X);
OK
OK
turborel: SQLCODE -201 (SQLSTATE 23000): CHECK ANN.SIZED_CHECK1 of ANN.SIZED is false for a row [ISO/IEC 9075:1992 §11.6 GR3]
IT
-----------
valid
not valid
unknown
(3 rows)
The validate expression
validate [ lax | strict ] { <expression> }
XQuery's validate expression, in a query of XMLQUERY, XMLEXISTS or XMLTABLE, validates one document or element node and gives a new one, of no parent, whose elements and attributes carry their types, as XMLVALIDATE's do. It validates by the registered XML Schemas the user may use, of each target namespace the one of the least name, chosen as the statement is prepared: the element validated, the node or a document node's element, is validated by the one of its namespace, with whatever that one imports; of a namespace none of them has, INFORMATION_SCHEMA.XS, XSI or SQLXML. A view, a constraint, a trigger or a routine whose query has a validate expression keeps the ones chosen when it was created, and depends on each of them, as on a registered XML Schema it names.
strict, the default, needs a global declaration of the element. lax
takes one if there is one, else gives the element the type xs:anyType,
its attributes xdt:untypedAtomic, and validates each child element the
same way. A document node's IDs and identity constraints are checked;
an element node's are not. Each error is SQLSTATE 10000, the message
naming XQuery's code:
| Code | When |
|---|---|
err:XQTY0030 |
The expression is not one document or element node |
err:XQDY0061 |
A document node's children are not one element among comments and processing instructions |
err:XQDY0084 |
strict, and no registered XML Schema in scope declares the element |
err:XQDY0027 |
The node is not valid |
VALUES (XMLCAST(XMLQUERY('data((validate { . })/size) + 1' PASSING BY REF XMLPARSE(DOCUMENT '<size>12</size>' PRESERVE WHITESPACE) RETURNING SEQUENCE EMPTY ON EMPTY) AS INTEGER));
VALUES (XMLQUERY('validate { . }' PASSING BY REF XMLPARSE(DOCUMENT '<size>0</size>' PRESERVE WHITESPACE) RETURNING SEQUENCE EMPTY ON EMPTY));
VALUES (XMLQUERY('validate { <note/> }' RETURNING SEQUENCE BY REF EMPTY ON EMPTY));
VALUES (XMLSERIALIZE(CONTENT XMLQUERY('let $v := validate lax { <note>see <size>4</size></note> } return ($v instance of element(note, xs:anyType), data($v/size) instance of xs:positiveInteger)' RETURNING SEQUENCE BY REF EMPTY ON EMPTY) AS VARCHAR(20)));
--------------
13
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XQDY0027: [Schema1 §3.3.4 cvc-type.3.1.3] '0' is no value of the element's type (against registered XML Schema ANN.SIZES) [ISO/IEC 9075-14:2006 draft §6.18 GR3]
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XQDY0084: [XQuery §3.13] note has no top-level element declaration (against registered XML Schema ANN.SIZES) [ISO/IEC 9075-14:2006 draft §6.18 GR3]
----------------------
true true
(1 row)
Schema imports
import schema [ namespace <prefix> = | default element namespace ]
"<target namespace>" [ at "<location>" [ , "<location>" ]... ]
A query's prolog imports a registered XML Schema the user may use by
its target namespace: of several, the one whose schema location is one
of the locations after at, else the one of the least name,
INFORMATION_SCHEMA.XS, XSI or SQLXML after the others. The prefix is
bound to the namespace, or the namespace made the default element
namespace. The schema's type definitions, element declarations and
attribute declarations then name sequence types: element(*, p:t)
matches an element whose type is p:t or derived from it,
schema-element(p:e) one of the declaration p:e or of the
substitution group it heads, nilled only if p:e is nillable, and
schema-attribute(p:a) an attribute of the declaration p:a. A view,
a constraint, a trigger or a routine depends on what its queries
import.
A simple type the schema defines, restricting a built-in atomic type,
is an atomic type of the query as the built-in ones are. $v instance of p:t holds of a value of p:t or of a type derived from it,
treat as, typeswitch and a function's parameters and result take it,
and cast as p:t and the constructor function p:t(...) make a value
of it, checking the type's facets: a value they refuse fails with
err:FORG0001. A string is read as the type's literal, its whitespace
normalized and its patterns matched first; another value is cast to the
built-in type the type restricts, then its patterns are matched against
its canonical form. A cast to a type derived from xs:QName is of a
string literal alone. The typed value of an element or attribute a
validation typed is of its own type, a list's items of the item type
and a union's of the member that validated each, and a value keeps its
type when stored. Otherwise a value of p:t is a value of the built-in
type it restricts: it compares, computes and serializes as one, and a
cast to that type keeps the value and drops p:t. A list or union type
is no atomic type (err:XPST0051).
| Code | When |
|---|---|
err:XQST0058 |
The prolog imports one target namespace twice |
err:XQST0059 |
No registered XML Schema the user may use has the target namespace |
err:XQST0035 |
Two schemas imported define one name from different schema documents |
err:XQST0057 |
A prefix is bound to no target namespace |
VALUES (XMLSERIALIZE(CONTENT XMLQUERY('import schema default element namespace ""; (validate { . })/size instance of schema-element(size)' PASSING BY REF XMLPARSE(DOCUMENT '<size>12</size>' PRESERVE WHITESPACE) RETURNING SEQUENCE EMPTY ON EMPTY) AS VARCHAR(10)));
VALUES (XMLQUERY('import schema "urn:nowhere"; 1' RETURNING SEQUENCE BY REF EMPTY ON EMPTY));
CREATE XML SCHEMA SHOES AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:s="urn:shoes" targetNamespace="urn:shoes"><xs:simpleType name="shoesize"><xs:restriction base="xs:integer"><xs:minInclusive value="1"/><xs:maxInclusive value="50"/></xs:restriction></xs:simpleType><xs:element name="shoe" type="s:shoesize"/></xs:schema>';
VALUES (XMLSERIALIZE(CONTENT XMLQUERY('import schema namespace s = "urn:shoes"; let $v := data((validate { . })/s:shoe) return ($v instance of s:shoesize, $v + 1, s:shoesize("42") instance of xs:integer, 42 instance of s:shoesize)' PASSING BY REF XMLPARSE(DOCUMENT '<shoe xmlns="urn:shoes">44</shoe>' PRESERVE WHITESPACE) RETURNING SEQUENCE EMPTY ON EMPTY) AS VARCHAR(40)));
VALUES (XMLQUERY('import schema namespace s = "urn:shoes"; 51 cast as s:shoesize' RETURNING SEQUENCE BY REF EMPTY ON EMPTY));
------------
true
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XQST0059: no registered XML Schema the current user may use has the target namespace 'urn:nowhere' [XQuery §4.10] [ISO/IEC 9075-14:2006 draft §6.18 GR3] (at character 8)
OK
------------------------------------------
true 45 true false
(1 row)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error: '51' is not valid for {urn:shoes}shoesize: it is not within its maxInclusive [Schema2 §4.3.7.3] 50 [Schema2 §4.1.4] [XQueryFO §17.1.1] err:FORG0001 [ISO/IEC 9075-14:2006 draft §6.18 GR3]
XQuery modules
import module [ namespace <prefix> = ] "<target namespace>"
[ at "<location>" [ , "<location>" ]... ]
A query's prolog imports an XQuery library module registered by CREATE
XQUERY MODULE by its target
namespace, and calls its functions and reads its variables. Of the
modules of that namespace the user may use, it imports the one whose
location, or name, alone or after its schema's, is one of the locations
after at, else the one of the least name. The prefix is bound to the
namespace.
A query imports a module's own functions and variables, not those of the
modules it imports. Each module is analysed in its own static context: a
module sees none of the namespaces the statement declares, and its
functions keep its own setters, such as its construction mode. A library
module imports the modules it imported as it was registered, whoever
runs the query, so a user needs USAGE alone on the modules its query
imports. A module imports modules registered before it, or, once
ALTER XQUERY MODULE replaces its
text, after it, so modules of one namespace may import one another and
call each other's functions; a cycle of modules not all of one namespace
is refused (err:XQST0073). The modules a user may use are those of its own schemas and
those it holds USAGE on (GRANT). A view, a
constraint, a trigger or a routine depends on the modules its queries
import, and keeps them: it imports those it imported as it was defined,
never one registered later.
| Code | When |
|---|---|
err:XQST0047 |
The prolog imports one target namespace twice |
err:XQST0059 |
No registered XQuery module the user may use has the target namespace |
err:XQST0034 |
A function imported has the name and arity of one declared or imported |
err:XQST0049 |
A variable imported has the name of one declared or imported |
err:XQST0036 |
A function or variable imported is of a schema type the prolog does not import |
err:XQST0073 |
Modules import one another, not all of one namespace |
err:XQST0088 |
The target namespace is the empty string |
CREATE XQUERY MODULE ORDERS_LIB AS
'module namespace o = "urn:orders";'
'declare function o:total($order as element(order)) as xs:double { fn:sum($order/item/@price) };';
SELECT ID, XMLCAST(XMLQUERY('import module namespace o = "urn:orders" at "ORDERS_LIB"; o:total(/order)' PASSING BY REF DOC RETURNING CONTENT NULL ON EMPTY) AS INTEGER) FROM ORDERS2 ORDER BY ID;
VALUES (XMLQUERY('import module "urn:nowhere"; 1' RETURNING SEQUENCE BY REF EMPTY ON EMPTY));
OK
ID |
--------------+--------------
1 | 35
2 | 5
(2 rows)
turborel: SQLCODE -301 (SQLSTATE 10000): XQuery error err:XQST0059: no registered XQuery module the module may import has the target namespace 'urn:nowhere' [XQuery §4.11] [ISO/IEC 9075-14:2006 draft §6.18 GR3] (at character 8)
Clients
A column of an XML type is described with its modifiers. A value is sent as its serialization, and the client library parses it when its nodes are needed. A value with no serialization, such as an attribute standing alone, is refused with SQLSTATE 2200W when it is fetched.
Limits
| Limit | Value |
|---|---|
| Elements nested | 512 |
| Text in one tree | 16 MiB |
| Nodes and namespace bindings in one tree | 1,048,576 |
| Text entities add to one parse | 16 MiB |
| Text XMLPARSE reads | 64 MiB |
| A value's stored form | 64 MiB |
| XMLMAP's data, of all its tables, and its schema document | 64 MiB |
| Elements and attributes an XML index keys of one value | 262,144 |
| Octets of a value an XML index keys | 64; 512 of XML_PRIMARY |
| Expressions nested in an XQuery query | 64 |
| Operations and function calls nested as a query is evaluated | 256 |
| Expressions nested as a query is statically typed | 256 |
| How deep a static type nests | 256 |
| Item types the descendants or ancestors of a static type are of | 4,096 |
| Items of one sequence a query makes | 1,048,576 |
| Text of one string a query makes | 16 MiB |
| What the nodes one evaluation of a query constructs hold | 64 MiB |
A value or query past a limit fails with SQLCODE -900.