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.