Functions › Scalar Functions › XML Functions › Descriptions of Functions › XMLFOREST

XMLFOREST

This function returns an XML value consisting of a forest, or collection, of XML elements, given a list of "forest elements." Each forest element generates an XML element using the referenced column name or, if provided, the forest element name (the identifier in the syntax diagram that follows) as the XML element name and the forest element value (the value expression in the following diagram) as the element content.

Following is the syntax diagram for XMLFOREST:

                   ┌─ , ──────────────┐
├── XMLFOREST ─ ( ─▼─ forest-element ─┴───────────────────────────────────────►

 ►─┬───────────────────────────────┬─ ) ───────────────────────────────────────┤
   └─ OPTION ─ xml-content-option ─┘

Expansion of Where forest-element is as follows

├── value-expression ─┬───────────────────┬────────────────────────────────────┤
                      └─ AS ─ identifier ─┘

Expansion of Where xml-content-option is as follows

├──┬─ NULL ON NULL ◄ ────┬─────────────────────────────────────────────────────┤
   ├─ EMPTY ON NULL ─────┤
   ├─ ABSENT ON NULL ────┤
   ├─ NIL ON NULL ───────┤
   └─ NIL ON NO CONTENT ─┘

The xml-content-option in the XMLFOREST function is applied separately to each forest-element. In XMLFOREST, EMPTY ON NULL is the default. For a further description of the xml-content-option, see the paragraph following the XMLELEMENT syntax diagram in XMLATTRIBUTES.

The following example generates a "Vendor" element for each vendor. The name of the vendor is used as an attribute, and two sub-elements are created from columns contactName and contactPhone using the XMLFOREST function.

SELECT vendorId,
       XMLSERIALIZE(CONTENT
                    XMLELEMENT(NAME "Vendor",
                               XMLATTRIBUTES(v.vendorDBA AS DBA),
                               XMLFOREST (v.contactName,
                                          v.contactPhone AS phone)
                              )
                    AS VARCHAR(300)) AS "vendorContacts"
  FROM vendors v;

The result of the previous example follows:

vendorId  vendorContacts
INTEGER   VARCHAR(300)
--------  ---------------------------------------------
       1  <-Vendor DBA="Best Office Supplies">
              <-contactName>Joeseph Dudely<-/contactName>
              <-phone>123-456-7890<-/phone>
           <-/Vendor>
       2  <-Vendor DBA="Widgets R Us">
              <-contactName>Mary Doeright<-/contactName>
              <-phone>123-456-1313<-/phone>
          <-/Vendor>
-------------------------------------------------------