Using the XMLELEMENT function

The following query generates nested XML, producing a <product_info> element for each product, with elements that provide the name, quantity, and description of each product:

SELECT ID,
XMLELEMENT( NAME product_info,
            XMLELEMENT( NAME item_name, Products.name ),
            XMLELEMENT( NAME quantity_left, Products.Quantity ),
            XMLELEMENT( NAME description, Products.Size || ' ' ||
                        Products.Color || ' ' || Products.name )
          ) AS results
FROM Products
WHERE Quantity > 30;

This query produces the following result:

ID

results

301

<product_info>
 <item_name>Tee Shirt
  </item_name>
 <quantity_left>54
  </quantity_left>
 <description>Medium Orange
  Tee Shirt</description>
</product_info>

302

<product_info>
 <item_name>Tee Shirt
  </item_name>
 <quantity_left>75
  </quantity_left>
 <description>One Size fits
  all Black Tee Shirt
  </description>
</product_info>

400

<product_info>
 <item_name>Baseball Cap
  </item_name>
 <quantity_left>112
  </quantity_left>
 <description>One Size fits
  all Black Baseball Cap
  </description>
</product_info>

...

The XMLELEMENT function allows you to specify the content of an element. The following statement produces an XML element with the content hat.

SELECT ID, XMLELEMENT( NAME product_type, 'hat' )
FROM Products
WHERE Name IN ( 'Baseball Cap', 'Visor' );

You can add attributes to the elements by including the XMLATTRIBUTES argument in your query. This argument specifies the attribute name and content. The following statement produces an attribute for the name, Color, and UnitPrice of each item.

SELECT ID, XMLELEMENT( NAME item_description,
                       XMLATTRIBUTES( Name,
                                       Color,
                                       UnitPrice )
                      ) AS item_description_element
FROM Products
WHERE ID > 400;

Attributes can be named by specifying the AS clause:

SELECT ID, XMLELEMENT( NAME item_description,
                       XMLATTRIBUTES ( UnitPrice AS
                                        price ),
                       Products.name
                     ) AS products
FROM Products
WHERE ID > 400;

For more information, see XMLELEMENT function [String].

The following example uses XMLELEMENT with an HTTP web service.

ALTER PROCEDURE "DBA"."http_header_example_with_table_proc"()
RESULT ( res LONG VARCHAR )
BEGIN
   DECLARE var LONG VARCHAR;
   DECLARE varval LONG VARCHAR;
   DECLARE I INT;
 DECLARE res LONG VARCHAR;
 DECLARE tabl XML;
   SET var  = NULL;
loop_h:  
   LOOP
      SET var = NEXT_HTTP_HEADER( var );
      IF var IS NULL THEN LEAVE leave loop_h END IF;
      SET varval = http_header( var );
      -- ... do some action for <var,varval> pair...
        SET tabl = tabl || 
                        XMLELEMENT( name "tr", 
                        XMLATTRIBUTES( 'left' AS "align", 'top' AS "valign" ),
                        XMLELEMENT( name "td", var ),
                        XMLELEMENT( name "td", varval ) )  ;
         
   END LOOP;

    SET res = XMLELEMENT( NAME "table",
                XMLATTRIBUTES( '' AS "BORDER", '10' as "CELLPADDING", '0' AS "CELLSPACING" ),

                XMLELEMENT( NAME "th", 
                    XMLATTRIBUTES( 'left' AS "align", 'top' AS "valign" ),
                    'Header Name' ), 
                
                XMLELEMENT( NAME "th", 
                    XMLATTRIBUTES( 'left' AS "align", 'top' AS "valign" ),
                    'Header Value' ),
           
                 tabl ) ;
    SELECT res;
END

Using the XMLELEMENT function

Generating nested elements

Specifying element content

Generating elements with attributes

Example