Use of 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 ( Color AS color,
                                      UnitPrice AS price ),
                       Products.Name
                     ) AS products
FROM Products
WHERE ID > 400;

The following example uses XMLELEMENT with an HTTP web service.

CREATE OR REPLACE 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 htmltable XML;
    SET var  = NULL;
loop_h:  
    LOOP
        SET var = NEXT_HTTP_HEADER( var );
        IF var IS NULL THEN LEAVE loop_h END IF;
        SET varval = http_header( var );
        -- ... do some action for <var,varval> pair...
        SET htmltable = htmltable || 
            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' ),
           
        htmltable);
    SELECT res;
END;

XMLELEMENT function [String]

Use of the XMLELEMENT function

Generating nested elements

Specifying element content

Generating elements with attributes

Example

See also