XMLELEMENT scalar function

1. 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.
2. If the xmlattributes-function is specified, it is evaluated and the result is a sequence of attribute nodes.
3. 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.
4. 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.
5. 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.
6. Element, text, comment, and processing instruction nodes in the content sequence become the children of the constructed element node.
7. The constructed element node is given a type annotation of xs:anyType, and each of its attributes is given a type annotation of xdt:untypedAtomic. The node name of the constructed element node is element-name specified after the NAME keyword.

• 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:

     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"
       )
     )

  returns:

  <c xmlns:tst="www.ipo.com" tst:b="2"/>

  A second example:

     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"
       )
     )

  returns:

  <tst:c xmlns:tst="www.tst.com" xmlns:db2ns-a1="www.ipo.com"
    db2ns-a1:b="2"/>

   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>

   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>

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>