1.14.3 SQL/XML scalar functions
(1) XMLQUERY
(a) Function
Evaluates XQuery expressions and generates an XML type value as the result.
(b) Format
XMLQUERY ( XQuery-query
PASSING BY VALUE XML-query-argument [, XML-query-argument]...
[RETURNING SEQUENCE [BY VALUE]]
EMPTY ON EMPTY )
XQuery-query ::= character-string-literal
XML-query-argument ::= {XML-query-context-items | XML-query-variable}
XML-query-context-items ::= value-expression [BY VALUE]
XML-query-variable ::= value-expression AS XQuery-variable-identifier [BY VALUE] |
(c) Operands
- XQuery-query ::= character-string-literal
Specifies the XQuery query to evaluate as a character string literal. For details about how to specify queries in XQuery, see 1.15 XQuery.
- XML-query-argument ::= {XML-query-context-items|XML-query-variable}
Specifies the argument to pass to the XQuery query.
The parent properties of the XQuery sequence items that are passed to the XQuery query as the XML query argument are empty. If you code nodes representing the same part of the same XML type value in different XML query arguments, they are treated as different nodes in the XQuery evaluation.
- XML-query-context-items ::= value-expression [BY VALUE]
Specifies the context items to be evaluated for the XQuery query.
If not specified, the XQuery query specified in XQuery-query is evaluated once, and no context items are set.
Note that only one such item can be specified in the PASSING clause.
- value-expression
- Specifies a value expression whose result evaluates to an XML type value.
- Each XQuery item in the sequence that makes up the XML type value resulting from this value expression will be a context item in the XQuery evaluation.
- If the result of the value expression is the null value, the result of the XMLQUERY function is also the null value.
- The following can be specified:
- Column specification
- Column from a named derived table that was derived from a column specification
- BY VALUE
- This format is supported only for compatibility with ISO standards. It has no effect on how a value resulting from a value expression specified in an XML query context item is returned.
- XML-query-variable::= value-expression AS XQuery-variable-identifier [BY VALUE]
Specified in order to pass a value to the XQuery variable specified in the XQuery query
- value-expression
- Specifies the value expression to pass to the XQuery variable specified in the XQuery query.
- If the result of the value expression is not the XML type, it is converted to an XQuery sequence whose value is the XML type before being passed.
- The following table lists the data types that can be specified and their formats after conversion.
Table 1-39 Data types of values passed to XQuery variables and their XML type value formats after conversion
Data type | Format after conversion |
---|
INTEGER | Value of type xs:int# |
SMALLINT | Value of type xs:int# |
DECIMAL | Value of type xs:decimal# |
FLOAT | Value of type xs:double# |
SMALLFLT | Value of type xs:double# |
CHAR | Value of type xs:string# |
VARCHAR | Value of type xs:string# |
MCHAR | Value of type xs:string# |
MVARCHAR | Value of type xs:string# |
DATE | Value of type xs:date# |
TIME | Value of type xs:time# |
TIMESTAMP | Value of type xs:dateTime# |
XML | Not converted (same format as the XML type value specified in the value expression) |
- #
- Becomes an XQuery sequence consisting of a single atomic value.
- An error results if the XQuery sequence cannot be converted.
- If the result of the value expression is the null value, the value passed to the XQuery variable is an empty XQuery sequence.
- The following can be specified in the value expression:
- Literal
- USER, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation, date operation, time operation, or concatenation operation
- Set function
- Scalar function
- Function invocation
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- Column from a named derived table that was derived from any of the above value expressions
- AS XQuery-variable-identifier
- Specifies the identifier of the XQuery variable to which the value is passed.
- The XQuery variable identifier specifies an XQuery variable name that is specified in the XQuery query. For details about specifying names, see 1.1.7 Specification of names.
- The XML namespace of the XQuery variable identifier specified here is the default XML namespace of the XQuery query.
- The effective scope of the XQuery variable corresponding to the XQuery variable identifier specified here is the entire XQuery query.
- You cannot specify XQuery variable identifiers that are specified in other XML query variables in the same PASSING clause.
- BY VALUE
- This format is supported only for compatibility with ISO standards. It has no effect on how a value resulting from a value expression specified in an XML query variable is returned.
- Specify this only if the result of the value expression is the XML type. An error results if the result of the value expression is not the XML type.
- RETURNING SEQUENCE [BY VALUE]
- This format is supported only for compatibility with ISO standards. It has no effect on either the format or the return method of a value resulting from the XQuery evaluation.
(d) Rules
- The result is an XQuery sequence returned in an XML type value. The parent properties of the XQuery items comprising the XQuery sequence that is the resulting value is empty.
- This function can only be specified in places where function invocation is permitted.
- In an INSERT or UPDATE statement, the results of this function, or XML type values based on the results, cannot be stored in a column.
(e) Example
Retrieve book titles for the category specified in the embedded variable :category from the book_info column of BOOK_MANAGEMENT_TABLE.
SELECT XMLSERIALIZE(
XMLQUERY('/book_info[category=$CATEGORY]/title'
PASSING BY VALUE book_info,
:category AS CATEGORY
RETURNING SEQUENCE BY VALUE
EMPTY ON EMPTY)
AS VARCHAR(32000))
FROM BOOK_MANAGEMENT_TABLE
(2) XMLSERIALIZE
(a) Function
Generates a VARCHAR or BINARY type value out of an XML type value that has been serialized (converted to a string).
(b) Format
XMLSERIALIZE ( [CONTENT]
value-expression AS data-type
[VERSION '1.0']
[{INCLUDING XMLDECLARATION
|EXCLUDING XMLDECLARATION}]) |
(c) Operands
This format is supported only for compatibility with ISO standards. It has no effect on processing.
The result does not have to be in the format of a well-formed XML document.
Specify an XML type value from which to generate a VARCHAR or BINARY type value.
The following can be specified:
- Column specification
- XMLQUERY function
- XMLAGG set function
- Column from a named derived table that was derived from a column specification
Specify the data type of the result.
The following data types can be specified:
- VARCHAR type (cannot include a character set specification)
- BINARY type
An error results if the result cannot fit into the maximum length of the specified data type.
Specifies the XML version of the result. If omitted, '1.0' is assumed.
- [{INCLUDING XMLDECLARATION | EXCLUDING XMLDECLARATION}]
Specifies whether to include an XML declaration (example: <?xml version="1.0" encoding="UTF-8"?>) in the resulting XML code. If omitted, EXCLUDING XMLDECLARATION is assumed.
- INCLUDING XMLDECLARATION
- The resulting XML code includes the XML declaration.
- EXCLUDING XMLDECLARATION
- The resulting XML code does not include the XML declaration.
(d) Rules
- If the value expression is the null value, the result is also the null value.
- The character encoding of the result will be the character encoding specified in the pdntenv command (pdsetup command in the UNIX edition).
- The serialized result is a character string containing the concatenation of the results of every item in the XQuery sequence listed in value-expression being converted according to the rules listed below. The concatenation includes a single space character inserted between adjacent atomic values.
- The result of serializing a document node is the character string concatenation of the results of serializing the child nodes.
- The result of serializing an element node is the character string representation of that element node as described below. If the element node has child nodes, the result will be the serialized character string starting from the start tag. If the element node has no child node, the result will be the serialized empty element tag.
element-node-character-string-representation ::= {start-tag child-node-character-string-representation end-tag | empty-element-tag}
child-node-character-string-representation ::= {element-node-character-string-representation | processing-instruction-node-character-string-representation |
comment-node-character-string-representation | text-node-character-string-representation}
start-tag ::= < qualified-name [space XML-namespace-character-string-representation]...
[space attribute-node-character-string-representation]... >
end-tag ::= </ qualified-name >
empty-element-tag ::= < qualified-name [space XML-namespace-character-string-representation]...
[space attribute-node-character-string-representation]... /> |
XML-namespace-character-string-representation
A character string expressed in as an attribute node character string representation of an XML attribute that indicates an XML namespace, not specified in any ancestor element node, from among the valid XML namespaces in, or below, the current element node.
- The result of serializing an attribute node will be a character string representation of the attribute node, as follows:
attribute-node-character-string-representation ::= qualified-name equals-sign-operator "attribute-value" |
attribute-value
In the string value of the attribute node, the characters listed below will be replaced with their corresponding character strings.
Character to replace | Corresponding character string |
---|
& (ampersand) | & |
< (less-than sign) | < |
> (greater-than sign) | > |
" (double quotation mark) | " |
' (single quotation mark) | ' |
- The result of serializing a processing instruction node will be a character string representation of the processing instruction node, as shown below:
processing-instruction-node-character-string-representation::= <? processing-instruction-target space [processing-instruction-node-contents]?> |
- The result of serializing a comment node will be a character string representation of the comment node, as shown below:
comment-node-character-string-representation::= <!-- comment-node-contents --> |
- The result of serializing a text node will be a character string representation of the text node, as shown below:
text-node-character-string-representation::= text-value |
text-value
In the string value of the text node, the characters listed below will be replaced with their corresponding character strings.
Character to replace | Corresponding character string |
---|
& (ampersand) | & |
< (less-than sign) | < |
> (greater-than sign) | > |
- The result of serializing an atomic values will be a character string of the atomic value that has been converted to type xs:string, in which the characters listed below are replaced with their corresponding character strings.
Character to replace | Corresponding character string |
---|
& (ampersand) | & |
< (less-than sign) | < |
> (greater-than sign) | > |
- This function can only be specified in places where function invocation is permitted.
(e) Example
Retrieve the value of the book_info column from BOOK_MANAGEMENT_TABLE as a value of type VARCHAR.
SELECT XMLSERIALIZE(book_info AS VARCHAR(32000)
INCLUDING XMLDECLARATION) FROM BOOK_MANAGEMENT_TABLE
(3) XMLPARSE
(a) Function
Generates an XML type value from an XML document.
(b) Format
XMLPARSE( DOCUMENT value-expression
[AS data-type]
[WHITESPACE-specification] )
WHITESPACE-specification ::={PRESERVE | STRIP} WHITESPACE |
(c) Operands
Indicates that the XML document specified in the value expression is a well-formed XML document. This operand cannot be omitted.
Specifies a well-formed XML document from which to generate an XML type value. The following data types can be specified:
- CHAR type (cannot include a character set specification)
- VARCHAR type (cannot include a character set specification)
- MCHAR type
- MVARCHAR type
- BINARY type
Specifies the data type of the value expression. If the value expression is an embedded variable or a ? parameter, AS data-type is required. If AS data-type is specified, the value expression must be an embedded variable or a ? parameter.
- WHITESPACE-specification ::={PRESERVE | STRIP} WHITESPACE
Specifies how to handle whitespace in the XML document. Whitespace consists of space (X'20'), horizontal tab (X'09'), newline (X'0A'), and carriage return (X'0D'). If omitted, STRIP WHITESPACE is assumed.
Regardless of the WHITESPACE specification, all carriage return (X'0D') and combined carriage return-newline characters (X'0D0A') are replaced with a newline (X'0A') before processing WHITESPACE-specification.
- PRESERVE WHITESPACE
- Retains all whitespace.
- STRIP WHITESPACE
- The following normalization process is performed on whitespace contained in text nodes, except for the text node descendants of elements with the xml:space="preserve" attribute.
- Remove leading and trailing whitespace
- Replace contiguous whitespace with a single space (X'20')
(d) Rules
- If the value expression is the null value, the result is also the null value.
- This function can only be specified in places where function invocation is permitted.
- DTDs, XML schemas, comments, and processing instructions are ignored rather than processed as described above. The validity of the XML document specified in the value expression is not checked.
- The only entities that can be used are the predefined entities (< > & ' "). No internal or external entities other than these are permitted; if used, they will be treated as characters.
- Character code classifications that can be specified in the encoding attribute in the XML declaration are listed in the following table. By default, UTF-8 is assumed.
Encoding attribute of XML document | HiRDB character encoding |
---|
sjis (Shift JIS kanji) | ujis (EUC Japanese kanji) | utf-8 (Unicode (UTF-8)) |
---|
Shift_JIS | Y | N | N |
EUC-JP | N | Y | N |
UTF-8 | N | N | Y |
US-ASCII | Y | Y | Y |
Other than the above | N | N | N |
- Legend:
- Y: Can be specified.
- N: Cannot be specified.
- XML documents up to 5 megabytes in size can be specified in the value expression.
- Element names and attribute names up to 4,096 bytes in length can be defined.
- Up to 100 nested elements can be defined.
- When defining prefixes, the rules differ depending on the version of HiRDB XML Extension you use.
- HiRDB XML Extension versions earlier than 08-05:
Even if prefixes are defined, processing is performed as if no prefix were specified, and all elements and attributes are considered to be in the default XML namespace.
- HiRDB XML Extension version 08-05 or later:
Follows the environment assignments (XMLPARSE namespace processing specifications) for the XML Extension's XML data type plug-in. For details about environment assignments for the XML data type plug-in, see the manual HiRDB Version 9 XML Extension.
(e) Example
Insert the XML document stored in the BINARY type embedded variable :bookxml into BOOK_MANAGEMENT_TABLE.
INSERT INTO BOOK_MANAGEMENT_TABLE
VALUES(310494321, XMLPARSE(DOCUMENT :bookxml AS BINARY(32000)))
(f) Notes
- This function requires HiRDB XML Extension version 08-04 or later.
- If the character encodings of the HiRDB server and client are different, set the data type of the value expression to BINARY.