Like many days, I had a need to query data stored in Oracle. But today was different. I needed to return the data in an XML format. I started with a very simple need and generated the XML brute-force while traversing my cursor. However, I realized the data would not be escaped properly and knew/assumed Oracle has created built-in functionality to accomplish what I was trying to do.

I then recreated my logic using DBMS_XMLGEN. This worked well, in that it created the desired result set, but I hate the idea of storing SQL in a string to be passed into a function, which is precisely what DBMS_XMLGEN was having me do. I had a hard time figuring out the precise technique recommended by Oracle, or even the “latest” technique. My client’s environment was 9i, so I knew I wasn’t going to get to play with anything interesting 11g might have to offer.

My brief research showed that SQL/XML (SQLX) appeared to be the best option. A few hours later, it really has been nice. I have been able to create both simple and complex XML results. Once you have the syntax down, you can generate just about any XML structure you desire.

Let me know if you come across anything more interesting/useful than SQLX for querying Oracle RDBMS tables (not XML data stored natively in the DB), and I’ll be interested in checking it out.

Related Information:

1. XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX

   Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result...

2. Oracle DBMS_SCHEDULER vs DBMS_JOB (Create, Run, Monitor, Remove)

    DBMS_SCHEDULER is a newer, more complex job scheduling engine released in 10g, intended to replace DBMS_JOB going forward. DBMS_JOB,...

3. Oracle Rename Table - Example Syntax and Dependencies

   Renaming a table in Oracle is simple. Following is the generic Oracle table rename syntax: 1 ALTER TABLE current_table_name RENAME...

4. Applications with SQL Maintained Outside of Stored Procedures

   I have seen multiple custom solutions and packaged applications store their SQL outside of stored procedures. I am focusing this...

5. alter table, add column - Oracle Example Syntax

   Following is simple example syntax for adding a column to an Oracle table using “alter table” SQL: 1 2 ALTER...

6. Thoughts on Learning Oracle SQL: 1 - The Prelude

   So you want to learn SQL. Not a problem. You want to learn Oracle SQL, not a problem. You have...

7. Oracle Rename Column - alter table, rename column Example SQL Syntax

   Following is simple example syntax for renaming a column to an Oracle table using "alter table" SQL:
    
   ALTER...

8. Copy Table Columns and Data

   As you may know, in Oracle the syntax from creating a table that is the same as another is: 1...