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. Continue reading
In my last blog I briefly touched upon storing XML documents in MSSQL. Now let’s discuss creating them instead. Specifically I mean creating them using the FOR XML keywords in MSSQL. While this functionality exists in other database systems such as Oracle and DB2, their implementation is different and warrants a separate blog post.
Most of us are familiar with selecting data using a SQL command and retrieving some sort of result set. With XML being used so frequently for a wide range of applications, it is not uncommon for a programmer to want to build an XML message out of data stored in a database somewhere. Instead of using SQL queries and looping over result sets, it is possible in MSSQL to use the FOR XML declaration to actually return that data as pre-formatted XML. Continue reading