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.
[sql]
SELECT XMLElement("Supplier_Number",segment1) Number
,XMLElement("Supplier_Name",vendor_name) Name
FROM ap_suppliers
WHERE vendor_id IN (1,2);
[/sql]
The output will look like below. Since there are two suppliers, we will get the output in two rows.
[xml]
<Number>1</Number> <Name>Supplier 001</Name>
<Number>2</Number> <Name>Supplier 002</Name>
[/xml]
You can also use the XMLElement function to generate nested XML like below.
[sql]
SELECT XMLElement("Supplier",XMLElement("Number",segment1)
,XMLElement("Name",vendor_name))
FROM ap_suppliers
WHERE vendor_id IN (1,2);
[/sql]
The output will look like this
[xml]
<Supplier> <Number>1</Number> <Name>Supplier 001</Name> </Supplier>
<Supplier> <Number>2</Number> <Name>Supplier 002</Name> </Supplier>
[/xml]
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.
[sql]
SELECT XMLForest(segment1 AS "Number") Number
,XMLForest(vendor_name AS "Name") Name
FROM ap_suppliers
WHERE vendor_id IN (1,2);
[/sql]
The output will look like below.
[xml]
<Number>1</Number> <Name>Supplier 001</Name>
<Number>2</Number> <Name>Supplier 002</Name>
[/xml]
If you wish to generate nested XML, you may use the following SQL Statement.
[sql]
SELECT XMLElement("Supplier",XMLForest(segment1 AS "Number")
,XMLForest(vendor_name AS "Name"))
FROM ap_suppliers
WHERE vendor_id IN (1,2);
[/sql]
The output will look like this
[xml]
<Supplier> <Number>1</Number> <Name>Supplier 001</Name> </Supplier>
<Supplier> <Number>2</Number> <Name>Supplier 002</Name> </Supplier>
[/xml]
XMLSequence () Function
Another way of generating XML is by using the XMLSequence () Function. Take a look at the below query.
[sql]
SELECT value(e).getClobVal()
FROM TABLE(XMLSequence(CURSOR(SELECT segment1 Number
,vendor_name Name
FROM ap_suppliers
WHERE vendor_id in (1,2)))) e;
[/sql]
The output will look like below:
[xml]
<ROW> <Number>1</Number> <Name>Supplier 001</Name> </ROW>
<ROW> <Number>2</Number> <Name>Supplier 002</Name> </ROW>
[/xml]
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.
[sql]
SELECT XMLElement("Supplier_Details",XMLAgg(XMLElement("Supplier",XMLElement("Number",segment1)
,XMLElement("Name",vendor_name)))).getClobVal()
FROM ap_suppliers
WHERE vendor_id IN (1,2);
[/sql]
We have used getClobVal () function to display the XML Output. Now take a look at the output (after formatting).
[xml]
<Supplier_Details>
<Supplier>
<Number>1</Number>
<Name>Supplier 001</Name>
</Supplier>
<Supplier>
<Number>2</Number>
<Name>Supplier 001</Name>
</Supplier>
</Supplier_Details>
[/xml]
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.