Tuesday, January 31, 2012

XMLQuery: The document that never was and the intermediate step error

Recently I got a help request by email stating that a snippet of SQL code didn't work. There wasn't much detail included about what went wrong, but to me it boiled down to a SELECT statement using a XMLQUERY function, all inside a SQL-based function. I had some minutes, so I gave it a try.

Many people start developing code with an easy skeleton, then extending it and adding the more complex stuff. Why not do the same for an XQuery?

db2 => values xmlquery('$FOO' passing '<a><b>small test</b></a>' as "FOO")
<a><b>small test</b></a>

The above the scaled down version of the easy skeleton. It seems to work well. Now let's extend it:

db2 => values xmlquery('$FOO/a/b' passing '<a><b>small test</b></a>' as "FOO")
SQL16011N  The result of an intermediate step expression in an XQuery path expression contains an atomic value. Error QName=err:XPTY0019.  SQLSTATE=10507

Hm, that doesn't look expected, or does it? Looking into the XQuery and DB2 error description doesn't help either if you are dealing with such problems. The only thing that really helps here is experience. And it says: Remember to process well-formed documents!
In the above statements we pass a string in for XQuery processing. The simple skeleton works because we just return the string. However, the XQuery processor refuses to apply step expressions ("a" and "b" in our case) to the value "$FOO" because it is NOT an XML document, but a string. Hence the error message with the hint "contains an atomic value" (the string). What can we do? We can pass in a valid XML document:

db2 => values xmlquery('$FOO/a/b' passing xmlparse(document '<a><b>small test</b></a>') as "FOO")
<b>small test</b>

To produce an XML document out of a string we just use the XMLPARSE function as shown. And suddenly everything seems clear. Remember to process well-formed documents!

Friday, January 20, 2012

DB2 and PL/SQL features

Today, I answered an email in which I was asked whether DB2 supports PL/SQL and such features as exception handling, the Oracle data dictionary (system catalog), triggers, etc. My response was that many features, especially the most important ones, are supported and that it has become fairly easy - compared to some years back - to move from Oracle to DB2. Many companies are considering this, often as a way to start a dual vendor strategy and to save money.

How do you find out what is supported and how do you find more information? Here are some links:
Happy reading and have a nice weekend...

Thursday, January 12, 2012

Hospitals - a place of extremes

A Happy New Year to everyone. The last week of 2011 was like the first week of 2011, I was in the hospital with one of my kids because of an epilepsy-related check-up (all is well). Hospitals are the place to be to experience life at its fullest extent.

It's the place where - for many people - life begins and/or ends. You will receive personalized treatment as long as it fits into the standard procedures (think of "customized" ERP software like SAP...). It is where absolute boredom and highest suspense and action meet. Hospitals are thoroughly organized through all kinds of processes, they are certified for quality, for hygiene, etc., but they can also be the most chaotic place. If you want to meet new kinds of viruses and bacteria, go to a hospital even though they have disinfection and sanitation processes much more than any other place.

Hospitals also have all kinds of technology which helps both efficiency and medical results, but the human is at the core and without highly experienced humans the technology does not help at all. And in terms of data management you will find traditional relational systems, content management, BI systems, image processing, video and sound processing, archiving, and so forth. And a lot of paper, all kinds of paper.

Health care is a very old profession and always at the edge of technology.

With all that in mind I wish you both a very interesting year full of action and a quiet, relaxed year. The right mix will do...