3 ways to turn SQL query results into XML

View All Scripts Login to Run Script

Script Name 3 ways to turn SQL query results into XML
Description Methods for turning SQL result sets into XML.
Area SQL General / XML
Contributor Chris Saxon (Oracle)
Created Thursday July 13, 2017

Statement 1

create table toys (  
  toy_id   integer,   
  toy_name varchar2(30),  
  price    number,  
  colour   varchar2(30)  
)

Table created.

Statement 2

begin 
  insert into toys values (1, 'Cheapasaurus Rex', 0.99, 'blue'); 
  insert into toys values (2, 'Costsalottasaurs', 99.99, 'green'); 
  insert into toys values (3, 'Bluesaurus', 21.99, 'blue');   
  commit; 
end;

1 row(s) inserted.

Statement 3

select * from toys


TOY_ID TOY_NAME PRICE COLOUR
1 Cheapasaurus Rex .99 blue
2 Costsalottasaurs 99.99 green
3 Bluesaurus 21.99 blue

3 rows selected.

TOY_ID	TOY_NAME	PRICE	COLOUR
1	Cheapasaurus Rex	.99	blue
2	Costsalottasaurs	99.99	green
3	Bluesaurus	21.99	blue

Statement 4

XMLForest converts each argument to an XML element. It then combines these into an XML fragment for each row in the input.

select xmlforest(toy_id, toy_name, price, colour).getClobVal() xdoc from toys


XDOC
<TOY_ID>1</TOY_ID><TOY_NAME>Cheapasaurus Rex</TOY_NAME><PRICE>.99</PRICE><COLOUR>blue</COLOUR>
<TOY_ID>2</TOY_ID><TOY_NAME>Costsalottasaurs</TOY_NAME><PRICE>99.99</PRICE><COLOUR>green</COLOUR>
<TOY_ID>3</TOY_ID><TOY_NAME>Bluesaurus</TOY_NAME><PRICE>21.99</PRICE><COLOUR>blue</COLOUR>

3 rows selected.

XDOC
<TOY_ID>1</TOY_ID><TOY_NAME>Cheapasaurus Rex</TOY_NAME><PRICE>.99</PRICE><COLOUR>blue</COLOUR>
<TOY_ID>2</TOY_ID><TOY_NAME>Costsalottasaurs</TOY_NAME><PRICE>99.99</PRICE><COLOUR>green</COLOUR>
<TOY_ID>3</TOY_ID><TOY_NAME>Bluesaurus</TOY_NAME><PRICE>21.99</PRICE><COLOUR>blue</COLOUR>

Statement 5

XMLAgg combines multiple XML fragments into a single document. The XMLElement call around XMLForest places each row in it's own ROW element. Without this all rows are at the same depth in the document, for example: 1Cheapasaurus Rex.99blue3Costsalottasaurs99.99green4Bluesaurus21.99blue

select xmlagg(xmlelement("ROW", xmlforest(toy_id, toy_name, price, colour))).getClobVal() xdoc  
from   toys


XDOC
<ROW><TOY_ID>1</TOY_ID><TOY_NAME>Cheapasaurus Rex</TOY_NAME><PRICE>.99</PRICE><COLOUR>blue</COLOUR></ROW><ROW><TOY_ID>2</TOY_ID><TOY_NAME>Costsalottasaurs</TOY_NAME><PRICE>99.99</PRICE><COLOUR>green</COLOUR></ROW><ROW><TOY_ID>3</TOY_ID><TOY_NAME>Bluesaurus</TOY_NAME><PRICE>21.99</PRICE><COLOUR>blue</COLOUR></ROW>

XDOC
<ROW><TOY_ID>1</TOY_ID><TOY_NAME>Cheapasaurus Rex</TOY_NAME><PRICE>.99</PRICE><COLOUR>blue</COLOUR></ROW><ROW><TOY_ID>2</TOY_ID><TOY_NAME>Costsalottasaurs</TOY_NAME><PRICE>99.99</PRICE><COLOUR>green</COLOUR></ROW><ROW><TOY_ID>3</TOY_ID><TOY_NAME>Bluesaurus</TOY_NAME><PRICE>21.99</PRICE><COLOUR>blue</COLOUR></ROW>

Statement 6

Passing a cursor to XMLType will return the results of the query as a single XML document.

select xmltype(cursor(select * from toys)).getClobVal() xdoc from dual


XDOC
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TOY_ID>1</TOY_ID>
  <TOY_NAME>Cheapasaurus Rex</TOY_NAME>
  <PRICE>.99</PRICE>
  <COLOUR>blue</COLOUR>
 </ROW>
 <ROW>
  <TOY_ID>2</TOY_ID>
  <TOY_NAME>Costsalottasaurs</TOY_NAME>
  <PRICE>99.99</PRICE>
  <COLOUR>green</COLOUR>
 </ROW>
 <ROW>
  <TOY_ID>3</TOY_ID>
  <TOY_NAME>Bluesaurus</TOY_NAME>
  <PRICE>21.99</PRICE>
  <COLOUR>blue</COLOUR>
 </ROW>
</ROWSET>

XDOC
<?xml version="1.0"?> <ROWSET> <ROW> <TOY_ID>1</TOY_ID> <TOY_NAME>Cheapasaurus Rex</TOY_NAME> <PRICE>.99</PRICE> <COLOUR>blue</COLOUR> </ROW> <ROW> <TOY_ID>2</TOY_ID> <TOY_NAME>Costsalottasaurs</TOY_NAME> <PRICE>99.99</PRICE> <COLOUR>green</COLOUR> </ROW> <ROW> <TOY_ID>3</TOY_ID> <TOY_NAME>Bluesaurus</TOY_NAME> <PRICE>21.99</PRICE> <COLOUR>blue</COLOUR> </ROW> </ROWSET>

Statement 7

dbms_xmlgen.getxml works in similar way to passing a cursor to XMLType: it executes the query and returns the result as a single XML document.

select dbms_xmlgen.getxml('select * from toys') xdoc from dual


XDOC
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TOY_ID>1</TOY_ID>
  <TOY_NAME>Cheapasaurus Rex</TOY_NAME>
  <PRICE>.99</PRICE>
  <COLOUR>blue</COLOUR>
 </ROW>
 <ROW>
  <TOY_ID>2</TOY_ID>
  <TOY_NAME>Costsalottasaurs</TOY_NAME>
  <PRICE>99.99</PRICE>
  <COLOUR>green</COLOUR>
 </ROW>
 <ROW>
  <TOY_ID>3</TOY_ID>
  <TOY_NAME>Bluesaurus</TOY_NAME>
  <PRICE>21.99</PRICE>
  <COLOUR>blue</COLOUR>
 </ROW>
</ROWSET>

XDOC
<?xml version="1.0"?> <ROWSET> <ROW> <TOY_ID>1</TOY_ID> <TOY_NAME>Cheapasaurus Rex</TOY_NAME> <PRICE>.99</PRICE> <COLOUR>blue</COLOUR> </ROW> <ROW> <TOY_ID>2</TOY_ID> <TOY_NAME>Costsalottasaurs</TOY_NAME> <PRICE>99.99</PRICE> <COLOUR>green</COLOUR> </ROW> <ROW> <TOY_ID>3</TOY_ID> <TOY_NAME>Bluesaurus</TOY_NAME> <PRICE>21.99</PRICE> <COLOUR>blue</COLOUR> </ROW> </ROWSET>

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database