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:
<data><contact><first>Klaus</first><last>Störtebeker</last><country>DE</country></contact><contact><first>Robin</first><last>Hood</last><country>GB</country></contact></data> 

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