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
In my previous blog post I talked about the SQL standard. It is tempting to visualize a standard as a list of rules nailed to a wall. However things in this industry have a habit of becoming a moving target. Continue reading
In a previous blog titled “Supercharging Your SQL Statements,” Fred Winkler touched upon some interesting capabilities inherent to the SQL Query Language. It is an unfortunate fact that while SQL as a standard is widely accepted, the actual implementation of that standard varies considerably between vendors.
Variation from the standard may not necessarily be a bad thing, although it is quite confusing to those who aren’t expecting it. Let’s look at the TRIM example Fred used.
SELECT TRIM(FIELD) from TABLE
That should be easy enough; however, MSSQL doesn’t support the use of the TRIM function. Instead, you have to use a LTRIM (Left Trim), RTRIM (Right Trim) or a combination of both.
SELECT (LTRIM(RTRIM(FIELD))) from TABLE
Not the most pleasant expression to work with, but it gets the job done. This is a good example where deviation from the standard may make the resulting script look more confusing. Continue reading