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 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...
3. 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...
4. 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...
5. 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...

6. MySQL and SQL Server - Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data Recursive queries are something I have used many, many times over the years to build dynamic, n-tiered hierarchies. Oracle’s CONNECT...
7. Oracle SUBSTR with INTSR Function - SQL Syntax Examples Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are...