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...


Stefan M. Mihokovic said...

Hallo Henrik,

because until now no one has replied, I've tried me times to solve the problem.

Here is my solution:

NAME "contact",
XMLELEMENT( NAME "first", fname ),
XMLELEMENT( NAME "last", lname ),
XMLELEMENT( NAME "country", cc )
FROM SQLQuiz.contacts
WHERE cc <> 'US'

Regards/Gruß Stefan

Henrik Loeser said...

Hello Stefan,

the solution you proposed will have the correct output. But there is a solution which only requires a single SQL/XML function to produce the output document.

Let's see who comes up with that solution...



Related Posts with Thumbnails