Oracle XML Generation

Even though Oracle is a relational database, it handles XML quite well. Specifically, its actually very efficient at generating XML from data in relational tables. There are a couple of different options when it comes to XML generation, using the dbms_xmlgen package or the SQL set of XML functions.

The dbms_xmlgen package provides a very easy to way to generate xml. Basically you give it a query and it gives you xml. Let's take a look:

1. declare  
2. ctx dbms_xmlgen.ctxHandle;  
3. xml xmlType;  
4. begin  
5.  ctx := dbms_xmlgen.newContext('select * from employees where employee_id = :emp_id');  
6.  dbms_xmlgen.setbindvalue(ctx,'emp_id',100);  
7.  xml := dbms_xmlgen.getXMLType(ctx);  
8.  dbms_output.put_line(substr(xml.getClobVal(),0,4000));  
9. end;  

And here is the result....

1. <ROWSET>  
2.  <ROW>  
3.   <EMPLOYEE_ID>100</EMPLOYEE_ID>  
4.   <FIRST_NAME>Steven</FIRST_NAME>  
5.   <LAST_NAME>King</LAST_NAME>  
6.   <EMAIL>SKING</EMAIL>  
7.   <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>  
8.   <HIRE_DATE>17-JUN-87</HIRE_DATE>  
9.   <JOB_ID>AD_PRES</JOB_ID>  
10.   <SALARY>24000</SALARY>  
11.   <DEPARTMENT_ID>90</DEPARTMENT_ID>  
12.  </ROW>  
13. </ROWSET>  

All right so that is pretty cool. Query in, XML out. This package has major pros and cons. Pros: For hierarchical data, specifically data created using a connect by statement, it really cannot be beat. (The method is called, newContextFromHierarchy, and you'll have to use the "level" psuedo-column in conjunction with the connect by to use that feature.) Cons: You really can't nest calls in there and expect it to be performant. Well you may ask, why would I want to nest those calls? In my experience, you'll need to pull data in from other tables to include as children xml elements on the main record. When you do that too much then you can have some problems with performance AND you may even experience actual memory errors from the underlying C code. Moral of the story: Just be careful if you are generating large amounts of XML. Another con, most of the cool features are 10g only, so you 9i'ers out there, will have to use the SQL XML functions.

Speaking of the SQL XML functions, that would be the second method of XML generation to discuss. If you look at these tags in the documentation, they are scary. No, really, like see your grandmother naked kind of scary. However, after you actually use the tags, they are very powerful and very performant even when generating lots of XML. Basically the tags you'll use most frequently are the xmlelement, xmlagg and xmlforest functions. Let's see them in action...

1. select xmlelement("ROWSET",  
2.           xmlagg(  
3.             xmlelement("ROW",  
4.               xmlforest(employee_id, first_name, last_name, email,  
5.                         phone_number, hire_date, job_id, salary,  
6.                         department_id)))) from employees   
7.                         where employee_id = 100;  

And the result is....

1. <ROWSET>  
2.   <ROW>  
3. <EMPLOYEE_ID>100</EMPLOYEE_ID>  
4. <FIRST_NAME>Steven</FIRST_NAME>  
5. <LAST_NAME>King</LAST_NAME>  
6. <EMAIL>SKING</EMAIL>  
7. <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>  
8. <HIRE_DATE>1987-06-17</HIRE_DATE>  
9. <JOB_ID>AD_PRES</JOB_ID>  
10. <SALARY>24000</SALARY>  
11. <DEPARTMENT_ID>90</DEPARTMENT_ID>  
12. </ROW>  
13. </ROWSET>  

Okay so basically the same XML we got from the dbms_xmlgen package. A little more work, sure, but your DBA's will love you for it. Let's analyze what each function does in a little more detail.
Xmlelement, basically takes a varchar as the element name ("name" is different from "NAME") and the data you want to xml-ize.
Xmlagg will basically take your row xml and concatenate each together in one big xml document.
Xmlforest takes the list of columns and xml-izes them all, using the column name or the alias that you give it as the tag name.

Using those 3 tags will generate most of the xml you will need, however there are others like xmlattribute and xmlcomment that may come in handy.

Good luck XMLing.