XMLELEMENT scalar function
The XMLELEMENT function returns an XML value that is an XQuery element node.
The schema is SYSIBM. The function name cannot be specified as a qualified name.
- NAME element-name
- Specifies the name of an XML element. The name is an SQL identifier that must be in the form of an XML qualified name, or QName (SQLSTATE 42634). See the W3C XML namespace specifications for more details on valid names. If the name is qualified, the namespace prefix must be declared within the scope (SQLSTATE 42635).
- xmlnamespaces-declaration
- Specifies the XML namespace declarations that are the result of
the XMLNAMESPACES declaration. The namespaces that are declared
are in the scope of the XMLELEMENT function. The namespaces apply
to any nested XML functions within the XMLELEMENT function, regardless
of whether or not they appear inside another subselect.
If xmlnamespaces-declaration is not specified, namespace declarations are not associated with the constructed element.
- xmlattributes-function
- Specifies the XML attributes for the element. The attributes are the result of the XMLATTRIBUTES function.
- element-content-expression
- The
content of the generated XML element node is specified by an expression or a list of
expressions. The data type of
element-content-expression cannot be a BINARY type, a
VARBINARY type, or a structured type (SQLSTATE 42884). The expression can be any
SQL expression.
If element-content-expression is not specified, an empty string is used as the content for the element and OPTION NULL ON NULL or EMPTY ON NULL must not be specified.
- OPTION
- Specifies additional options for constructing the XML element.
If no OPTION clause is specified, the default is EMPTY ON NULL XMLBINARY
USING BASE64. This clause has no impact on nested XMLELEMENT invocations
specified in element-content-expression.
- EMPTY ON NULL or NULL ON NULL
- Specifies whether a null value or an empty element is to be returned
if the values of each element-content-expression is
a null value. This option only affects null handling of element contents,
not attribute values. The default is EMPTY ON NULL.
- EMPTY ON NULL
- If the value of each element-content-expression is null, an empty element is returned.
- NULL ON NULL
- If the value of each element-content-expression is null, a null value is returned.
- XMLBINARY USING BASE64 or XMLBINARY USING HEX
- Specifies the assumed encoding of binary input data, character
string data with the FOR BIT DATA attribute, or a distinct type that
is based on one of these types. The encoding applies to element content
or attribute values. The default is XMLBINARY USING BASE64.
- XMLBINARY USING BASE64
- Specifies that the assumed encoding is base64 characters, as defined for XML schema type xs:base64Binary encoding. The base64 encoding uses a 65-character subset of US-ASCII (10 digits, 26 lowercase characters, 26 uppercase characters, '+', and '/') to represent every six bits of the binary or bit data with one printable character in the subset. These characters are selected so that they are universally representable. Using this method, the size of the encoded data is 33 percent larger than the original binary or bit data.
- XMLBINARY USING HEX
- Specifies that the assumed encoding is hexadecimal characters, as defined for XML schema type xs:hexBinary encoding. The hexadecimal encoding represents each byte (8 bits) with two hexadecimal characters. Using this method, the encoded data is twice the size of the original binary or bit data.
This function takes an element name, an optional collection of namespace declarations, an optional collection of attributes, and zero or more arguments that make up the content of the XML element. The result is an XML sequence containing an XML element node or the null value.
The data type of the result is XML. If any of the element-content-expression arguments can be null, the result can be null; if all the element-content-expression argument values are null and the NULL ON NULL option is in effect, the result is the null value.
Notes
- When constructing elements that will be copied as content of another element that defines default namespaces, default namespaces should be explicitly undeclared in the copied element to avoid possible errors that could result from inheriting the default namespace from the new parent element. Predefined namespace prefixes ('xs', 'xsi', 'xml', and 'sqlxml') must also be declared explicitly when they are used.
- Constructing an element node: The resulting element node
is constructed as follows:
- The xmlnamespaces-declaration adds a set of in-scope namespaces for the constructed element. Each in-scope namespace associates a namespace prefix (or the default namespace) with a namespace URI. The in-scope namespaces define the set of namespace prefixes that are available for interpreting QNames within the scope of the element.
- If the xmlattributes-function is specified, it is evaluated and the result is a sequence of attribute nodes.
- Each element-content-expression is evaluated
and the result is converted into a sequence of nodes as follows:
- If the result type is not XML, it is converted to an XML text
node whose content is the result of element-content-expression mapped
to XML according to the rules of mapping SQL data values to XML
data values (see the table that describes supported casts from non-XML
values to XML values in
Casting between data types
). - If the result type is XML, then in general the result is a sequence of items. Some of the items in that sequence might be document nodes. Each document node in the sequence is replaced by the sequence of its top-level children. Then for each node in the resulting sequence, a new deep copy of the node is constructed, including its children and attributes. Each copied node has a new node identity. Copied element and attribute nodes preserve their type annotation. For each adjacent sequence of one or more atomic values returned in the sequence, a new text node is constructed, containing the result of casting each atomic value to a string, with a single blank character inserted between adjacent values. Adjacent text nodes in the content sequence are merged into a single text node by concatenating their contents, with no intervening blanks. After concatenation, any text node whose content is a zero-length string is deleted from the content sequence.
- If the result type is not XML, it is converted to an XML text
node whose content is the result of element-content-expression mapped
to XML according to the rules of mapping SQL data values to XML
data values (see the table that describes supported casts from non-XML
values to XML values in
- The result sequence of XML attributes and the resulting sequences of all element-content-expression specifications are concatenated into one sequence which is called the content sequence. Any sequence of adjacent text nodes in the content sequence is merged into a single text node. If all the element-content-expression arguments are empty strings, or an element-content-expression argument is not specified, an empty element is returned.
- The content sequence must not contain an attribute node following a node that is not an attribute node (SQLSTATE 10507). Attribute nodes occurring in the content sequence become attributes of the new element node. Two or more of these attribute nodes must not have the same name (SQLSTATE 10503). A namespace declaration is created corresponding to any namespace used in the names of the attribute nodes if the namespace URI is not in the in-scope namespaces of the constructed element.
- Element, text, comment, and processing instruction nodes in the content sequence become the children of the constructed element node.
- The constructed element node is given a type annotation of
xs:anyType
, and each of its attributes is given a type annotation ofxdt:untypedAtomic
. The node name of the constructed element node is element-name specified after the NAME keyword.
- Rules for using namespaces within XMLELEMENT: Consider
the following rules about scoping of namespaces:
- The namespaces declared in the XMLNAMESPACES declaration are the in-scope namespaces of the element node constructed by the XMLELEMENT function. If the element node is serialized, then each of its in-scope namespaces will be serialized as a namespace attribute unless it is an in-scope namespace of the parent of the element node and the parent element is serialized too.
- If an XMLQUERY or XMLEXISTS is in an element-content-expression, then the namespaces becomes the statically known namespaces of the XQuery expression of the XMLQUERY or XMLEXISTS. Statically known namespaces are used to resolve the QNames in the XQuery expression. If the XQuery prolog declares a namespace with the same prefix, within the scope of the XQuery expression, the namespace declared in the prolog will override the namespaces declared in the XMLNAMESPACES declaration.
- If an attribute of the constructed element comes from an element-content-expression,
its namespace might not already be declared as an in-scope namespace
of the constructed element, in this case, a new namespace is created
for it. If this would result in a conflict, which means that the prefix
of the attribute name is already bound to a different URI by a in-scope
namespace, a prefix is generated that does not cause such a conflict
and the prefix used in the attribute name is changed to the new prefix,
and a namespace is created for this new prefix. The generated new
prefix follows the following pattern: "db2ns-xx", where "x" is a character
chosen from the set [A-Z,a-z,0-9]. For example:
returns:VALUES XMLELEMENT( NAME "c", XMLQUERY( 'declare namespace ipo="www.ipo.com"; $m/ipo:a/@ipo:b' PASSING XMLPARSE( DOCUMENT '<tst:a xmlns:tst="www.ipo.com" tst:b="2"/>' ) AS "m" ) )
A second example:<c xmlns:tst="www.ipo.com" tst:b="2"/>
returns:VALUES XMLELEMENT( NAME "tst:c", XMLNAMESPACES( 'www.tst.com' AS "tst" ), XMLQUERY( 'declare namespace ipo="www.ipo.com"; $m/ipo:a/@ipo:b' PASSING XMLPARSE( DOCUMENT '<tst:a xmlns:tst="www.ipo.com" tst:b="2"/>' ) AS "m" ) )
<tst:c xmlns:tst="www.tst.com" xmlns:db2ns-a1="www.ipo.com" db2ns-a1:b="2"/>
Examples
- Example 1: Construct an element with the NULL ON NULL option.
This query produces the following result:SELECT E.FIRSTNME, E.LASTNAME, XMLELEMENT( NAME "Emp", XMLELEMENT( NAME "firstname", E.FIRSTNME ), XMLELEMENT( NAME "lastname", E.LASTNAME ) OPTION NULL ON NULL ) AS "Result" FROM EMPLOYEE E WHERE E.EDLEVEL = 12
FIRSTNME LASTNAME Emp JOHN PARKER <Emp><firstname>JOHN</firstname> <lastname>PARKER</lastname></Emp> MAUDE SETRIGHT <Emp><firstname>MAUDE</firstname> <lastname>SETRIGHT</lastname></Emp> MICHELLE SPRINGER <Emp><firstname>MICHELLE</firstname> <lastname>SPRINGER</lastname></Emp>
- Example 2: Produce an element with a list of elements nested
as child elements.
This query produces the following result:SELECT XMLELEMENT( NAME "Department", XMLATTRIBUTES( E.WORKDEPT AS "name" ), XMLAGG( XMLELEMENT( NAME "emp", E.FIRSTNME ) ORDER BY E.FIRSTNME ) ) AS "dept_list" FROM EMPLOYEE E WHERE E.WORKDEPT IN ('A00', 'B01') GROUP BY WORKDEPT
dept_list <Department name="A00"> <emp>CHRISTINE</emp> <emp>SEAN</emp> <emp>VINCENZO</emp> </Department> <Department name="B01"> <emp>MICHAEL</emp> </Department>
- Example 3: Creating nested XML elements specifying a default
XML element namespace and using a subselect.
The statement returns the following XML document with the default element namespace declared in the root element:SELECT XMLELEMENT( NAME "root", XMLNAMESPACES(DEFAULT 'http://mytest.uri'), XMLATTRIBUTES(cid), (SELECT XMLAGG( XMLELEMENT( NAME "poid", poid ) ) FROM purchaseorder WHERE purchaseorder.custid = customer.cid ) ) FROM customer WHERE cid = '1002'
<root xmlns="http://mytest.uri" CID="1002"> <poid>5000</poid> <poid>5003</poid> <poid>5006</poid> </root>
- Example 4: Using a common table expression with XML namespaces.When an XML element is constructed with a common table expression and the element is used in elsewhere in the same SQL statement, any namespace declarations should be specified as part of the element construction. The following statement specifies the default XML namespace in both the common table expression that uses the PURCHASEORDER table to create the poid elements and the SELECT statement that uses the CUSTOMER table to create the root element.
The statement returns the following XML document with a default element namespace declared in the root element.WITH tempid(id, elem) AS (SELECT custid, XMLELEMENT(NAME "poid", XMLNAMESPACES(DEFAULT 'http://mytest.uri'), poid) FROM purchaseorder ) SELECT XMLELEMENT(NAME "root", XMLNAMESPACES(DEFAULT 'http://mytest.uri'), XMLATTRIBUTES(cid), (SELECT XMLAGG(elem) FROM tempid WHERE tempid.id = customer.cid ) ) FROM customer WHERE cid = '1002'
<root xmlns="http://mytest.uri" CID="1002"> <poid>5000</poid> <poid>5003</poid> <poid>5006</poid> </root>
In the following statement, the default element namespace is declared only in the SELECT statement that uses the CUSTOMER table to create the root element:
The statement returns the following XML document with the default element namespace declared in the root element. Because the poid elements are created in the common table expression without a default element namespace declaration, the default element namespace for the poid elements is not defined. In the XML document, the default element namespace for the poid elements is set to an empty string "" because the default element namespace for the poid elements is not defined, and the poid elements do not belong to the default element namespace of the root elementWITH tempid(id, elem) AS (SELECT custid, XMLELEMENT(NAME "poid", poid) FROM purchaseorder ) SELECT XMLELEMENT(NAME "root", XMLNAMESPACES(DEFAULT 'http://mytest.uri'), XMLATTRIBUTES(cid), (SELECT XMLAGG(elem) FROM tempid WHERE tempid.id = customer.cid ) ) FROM customer WHERE cid = '1002'
xmlns="http://mytest.uri"
.<root xmlns="http://mytest.uri" CID="1002"> <poid xmlns="">5000</poid> <poid xmlns="">5003</poid> <poid xmlns="">5006</poid> </root>