Tuesday, March 12, 2013

SQL Quiz: How to get this XML output?

Here is a small SQL quiz for you. I start with the following statements against a DB2 database:

drop table contacts;

create table contacts(
   id int unique not null,
   fname varchar(40),
   lname varchar(40),
   cc char(2));

create index cc_idx on contacts(cc);

insert into contacts values(1,'Klaus','Störtebeker','DE'),(2,'Bugs','Bunny','US'),(3,'Robin','Hood','GB'),(4,'Mickey','Mouse','US');

Next, I execute a query and the output is as follows:

And the question for you: What is an efficient way to produce the output above? Post your proposals as comments...