Create XML Using SQL Statements

In this post we will take a look at how to create XML using SQL Statements. The following functions will be used to generate the output in XML Format.

• XMLElement ()
• XMLForest ()
• XMLSequence ()
• XMLAgg ()

The combination of the above three functions will let us create some very fancy and complex XML Outputs. Lets start with a very simple example.

XMLEelement () Function

We need to generate the supplier details (supplier name and supplier number) in xml format. The following SQL Statement can be used.

SELECT  XMLElement("Supplier_Number",segment1)  Number
       ,XMLElement("Supplier_Name",vendor_name) Name
FROM    ap_suppliers
WHERE   vendor_id IN (1,2);

The output will look like below. Since there are two suppliers, we will get the output in two rows.

<Number>1</Number> <Name>Supplier 001</Name>
<Number>2</Number> <Name>Supplier 002</Name>

You can also use the XMLElement function to generate nested XML like below.

SELECT  XMLElement("Supplier",XMLElement("Number",segment1)
       ,XMLElement("Name",vendor_name))
FROM    ap_suppliers
WHERE   vendor_id IN (1,2);

The output will look like this

<Supplier> <Number>1</Number> <Name>Supplier 001</Name> </Supplier>
<Supplier> <Number>2</Number> <Name>Supplier 002</Name> </Supplier>

As you can see the Supplier Number and Supplier Name elements are now part of the parent group “Supplier”.

XMLForest () Function

We can use XMLForest to achieve the same results as XMLElement function. Lets take a look at the usage of XMLForest.

SELECT  XMLForest(segment1 AS "Number")  Number
       ,XMLForest(vendor_name AS "Name") Name
FROM    ap_suppliers
WHERE   vendor_id IN (1,2);

The output will look like below.

<Number>1</Number> <Name>Supplier 001</Name>
<Number>2</Number> <Name>Supplier 002</Name>

If you wish to generate nested XML, you may use the following SQL Statement.

SELECT  XMLElement("Supplier",XMLForest(segment1 AS "Number")
       ,XMLForest(vendor_name AS "Name"))
FROM    ap_suppliers
WHERE   vendor_id IN (1,2);

The output will look like this

<Supplier> <Number>1</Number> <Name>Supplier 001</Name> </Supplier>
<Supplier> <Number>2</Number> <Name>Supplier 002</Name> </Supplier>

XMLSequence () Function

Another way of generating XML is by using the XMLSequence () Function. Take a look at the below query.

SELECT value(e).getClobVal()
  FROM TABLE(XMLSequence(CURSOR(SELECT  segment1 Number
                                       ,vendor_name Name
                                  FROM  ap_suppliers
                                 WHERE  vendor_id in (1,2)))) e;

The output will look like below:

<ROW> <Number>1</Number> <Name>Supplier 001</Name> </ROW>
<ROW> <Number>2</Number> <Name>Supplier 002</Name> </ROW>

XMLAgg () Function

XMLAgg() is an aggregation function and hence produces one aggregated XML result for each group. If there is no group by specified in the query, then it returns a single aggregated XML result for all the rows of the query. NULL values are dropped from the result.

In all the examples that we have seen, the results returned are multiple rows. What if I need all the results combined into one row. To achieve this we can use the XMLAgg function along with XMLElement / XMLForest functions. Lets take a look.

SELECT  XMLElement("Supplier_Details",XMLAgg(XMLElement("Supplier",XMLElement("Number",segment1)
       ,XMLElement("Name",vendor_name)))).getClobVal()
FROM    ap_suppliers
WHERE   vendor_id IN (1,2);

We have used getClobVal () function to display the XML Output. Now take a look at the output (after formatting).

<Supplier_Details>
   <Supplier>
      <Number>1</Number>
      <Name>Supplier 001</Name>
   </Supplier>
   <Supplier>
      <Number>2</Number>
      <Name>Supplier 001</Name>
   </Supplier>
</Supplier_Details>

As you can see we get one row with all the results grouped together.

For more details on these functions, you can check out the Oracle Documentation here.
Hope this helps you to start using these functions. Please leave your suggestions, comments and feedback. Thanks for reading.