You're reading...
PL/SQL

Generate XML from a query in Oracle

XML can be easily generated from the database for use with other applications. The easiest way to do so is shown below. This method calls the built in function DBMS_XMLGEN.
XML is required from the following SQL
SELECT check_number, status_lookup_code, attribute12, attribute13
  FROM ap_checks_all
WHERE check_number IN(60708421,60708422,60708423)
Generate XML by running the query below
SELECT DBMS_XMLGEN.getxml
          ('
SELECT check_number, status_lookup_code, attribute12, attribute13
  FROM ap_checks_all
WHERE check_number IN (60708421, 60708422, 60708423)
'      )
  FROM DUAL
Generate XML
 
The XML generated in TOAD looks like this and the actual XML is given below the image.
Generated XML
The actual XML generated in TOAD is given below.
<?xml version="1.0"?>
<ROWSET>
<ROW>
  <CHECK_NUMBER>60708421</CHECK_NUMBER>
  <STATUS_LOOKUP_CODE>NEGOTIABLE</STATUS_LOOKUP_CODE>
  <ATTRIBUTE13>Y</ATTRIBUTE13>
</ROW>
<ROW>
  <CHECK_NUMBER>60708422</CHECK_NUMBER>
  <STATUS_LOOKUP_CODE>CLEARED BUT UNACCOUNTED</STATUS_LOOKUP_CODE>
  <ATTRIBUTE12>Y</ATTRIBUTE12>
  <ATTRIBUTE13>Y</ATTRIBUTE13>
</ROW>
<ROW>
  <CHECK_NUMBER>60708423</CHECK_NUMBER>
  <STATUS_LOOKUP_CODE>CLEARED BUT UNACCOUNTED</STATUS_LOOKUP_CODE>
  <ATTRIBUTE12>Y</ATTRIBUTE12>
  <ATTRIBUTE13>Y</ATTRIBUTE13>
</ROW>
</ROWSET>

 

This is a quick and easy way to  generate the XML. The disadvantage to this method is that the XML tags cannot be set by the developer. The XML tags will always be the same as the column header. We can make a more sophisticated program to generate the XML the way we want to. I shall put in on another post.
About these ads

About Abhijit

I love sleeping, watching Hollywood blockbusters, my Wii, road trips and watching my 4 year old son grow up. In between I try to squeeze in some time to go to work.

Discussion

2 thoughts on “Generate XML from a query in Oracle

  1. hi abhijit da,
    very nice blog…thanks for this effort… keep posting.

    .Prodipto.

    Posted by Prodipto | March 17, 2013, 1:18 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Traffic

Site Stats

  • 728,289 views since Feb 2012

Archives

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 215 other followers

Flags

Free counters!
Follow

Get every new post delivered to your Inbox.

Join 215 other followers

%d bloggers like this: