In a previous posting (Creating XML in MSSQL : http://www.extol.com/blog/?p=651) I described the feature in MSSQL which allows for generation of XML data with a specially structured database query. Other databases offer similar capabilities, so this post is meant to highlight how do accomplish the same task using Oracle.
For the purposes of this post I’ll be referring to Oracle 11g, which improved on the XML handling in 10g, and included features such as a binary XML data type superior XQuery support, and the ability to embed XML within PL/SQL.
Lets use the same example table as my previous blog.
Table Name: DEMO
Columns : | field1 | field2 |
Values: | ‘field1Text’ | ‘field2Text’
| ‘moreField1Text’ | ‘moreField2Text’
So how do we generate XML output from this table? Well, Oracle provides a package called DBMS_XMLGEN that has many convenient functions for formatting and generating XML:
So for example if I wanted to select everything from the DEMO table and return the value as XML it could be done like this:
“SELECT DBMS_XMLGEN.getXML(‘SELECT * FROM “DEMO”‘) FROM dual;
This would produce output like this:
As you can see from the query, the typical SQL that you would use to select these values has just been wrapped in another select statement to generate the SQL. That’s just a very basic example, but more complex queries will function in essentially the same way. The DBMS_XMLGEN package also has many useful functions to generate complex and XML directly from your existing database tables.
For more information on this feature and other ways to handle XML in Oracle, check out the Oracle XML DB Developer’s Guide: