As a I wrote a couple of days ago, I went to the second day of PostgreSQL Conference East 2008 last Sunday. I had a good time and really enjoyed meeting everyone, listening, learning, and occasionally talking. I asked a number of fearless-newbie questions that paid off handsomely: people were very willing to humor me. I also left with a beautiful t-shirt, mug, and bag combo thanks to EnterpriseDB. The bag has already been put to use for a grocery shopping trip.

Note to conference/website organizers: I can’t link to anything but the front page, so I assume my link above will someday point to the 2009 conference, or the 2008 West conference. It would be good to give each event a permalink right from the start…

One thing that surprised me was the distance …

Yes, that's correct, it's not a typo. Who needs XML over RDBMS?
So far, there has been a relatively small group of users who were demanding more XML features within or combined with MySQL. Perhaps it's because XML is not a big deal in the classic LAMP implementation of web-based services, but the more we penetrate the Enterprise market, the more XML is becoming an interesting topic. I am quite sure that we will move from the classic "nice to have" to the typical "commodity feature" in quite a short time, especially if we will improve our presence as the back end DB for J2EE apps and web services.

Let's be honest, the new nice XML features in 5.1 are still away from a good and proper XML coverage. They are great if we are going to store XML elements within columns of a table, but they are not really helpful if we need to use an XML interface to interact with our DB.

For what it's worth, although the fat 4 (Oracle, …

I was browsing around the web and ran across this article at xml.com, XML Moves to mySQL. Being a heavy XML user, I had to read the article. It looks like MySQL is expanding the built-in support for XML.

This article isn't very detailed but it links to Using XML in MySQL 5.1 and 6.0 at mySQL.com which is very detailed.

I like the ability that is built in to support loading XML from files. That's a feature I wish Oracle would work on. Even in 11g, that functionality is still limited.

MySQL also adds ExtractValue() and UpdateXML() support. If you're manipulating XML much, you know that these two functions are needed.

From what I know of MySQL and what I read here, MySQL is just getting started …

I’ve written an article highlighting some recent developments with XML support in MySQL 5.1 and MySQL 6.0. Topics include:

• Outputting MySQL data in XML format; includes a look at the 3rd-party lib_mysqludf_xql library
• Getting XML into MySQL; includes a discussion of the LOAD XML statement (new in MySQL 6.0)
• The ExtractValue() and UpdateXML() functions (new in MySQL 5.1)
• Security issues, including a look at a little nasty known as “XPath injection”

You can read it at the MySQL Developer Zone.

Alexander Barkov contributed a nifty stored procedure and a very helpful pre-publication review. Thanks, Bar!

Mark Atwood has published an interesting post with his wishlist for MySQL. One of his points is already (partly) covered with the LOAD XML command I made for MySQL 6.0 (or is it 5.2).

The main problem with it is perhaps that it works (like the LOAD DATA command) only with files, but in a web world you want it to be able to take input from other sources. You also need to be able to send an XML to several tables, with foreign keys coordinated etc.

Mark also has a wishlist for the opposite, the SELECT OUTFILE command. I have made a couple of stored procedures to help with this, but an extended SELECT OUTFILE would be mucke aesier to use and give much more possibilities. It would be fun to work on this, we'll see if I have the time, now that I am almost ready with my relocation..

I was very pleased when I heard about Oracle adding pivot functionality in select statements. Finally — we wouldn’t have to copy the data to a spreadsheet or code a ton of sum(case when col1 = 'X' then amount else 0 end) total_X for each column we would want to display.

I am basically looking for three things in a pivot-style query:

1. the ability to specify which column will be pivoted as one or more columns in the resulting query
2. row subtotals
3. column subtotals

The first item is the only one that really matters. I can work around the other two, so let’s get started.

Create a test table with a few rows:

create table bob_pivot_test( id number, cost number, tx_date date, product_type varchar2(50));
insert into bob_pivot_test values (1,10.99,sysdate-10,'Toy');
insert into bob_pivot_test values (1,10.99,sysdate-9,'Toy');
insert into bob_pivot_test values …

I'll be speaking on using Drupal with Adobe technologies such as Flex, AIR, and Flash. The event is totally free but you must register.

DrupalCampLA - http://upcoming.yahoo.com/event/221082/

A few weeks ago (time flies..) i posted a short note about my LOAD XML contribution. It has actually been available for some time, veven if it is not so easy to find. It is included in MySql 5.2.5:
download
documentation - quite good I think!

It is a bit sad that it is included in 5.2 (still in alfa) and not in 5.1. Since it is a separate command it will not affect existing installations and would not be risky. That faster it gets included the faster we will get feedback on it, which is what I believe is needed right now. Is this something that MySql users need and will use? Or will they use other tools to read XML data into their MySql databases?

LOAD XML reads data into a MySql table in much the same way as LOAD DATA does and in fact …

I got news from the MySQL people that my LOAD XML contribution is added to release 5.2. Check the manual page.

The aim of the contribution is to simplify reading of XML data into a MySQL database. The LOAD XML command supports three different xml formats:
- field values as attributes
- field values as tags
- the format produced by the mysql -x command, with the field name as an attribute and field value as a tag

If the LOAD XML command finds a matching field in the target table, the value is inserted, otherwise it is ignored. If you have used the LOAD DATA command (most of us have) you should recognize much of the functionality, LOAD XML works much the same way.

In a previous post I described how you could create XML output from SQL using stored functions. But that was a flat structure, with only one table and simple XML structure with a tag for every row in the result set, and column values as attributes or sub tags. Part of the real power of the XML format is that it supports more complicated structures, with hierarchical data. If you need to produce XML for integration with another application you will probably have to produce a XML with data from several tables, and a hierarchical structure. To accomplish this in MySQL we can use the GROUP_CONCAT function, combined with our XML stored functions.

An example:
We take our select statement from the previous post, that gives us a list of tables in xml format:

select xml_tag('table',null, concat(xml_attr('name',t.table_name),
xml_attr('engine',t.engine)),null)
from information_schema.tables t
where …