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

2 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:

SELECT XMLELEMENT( NAME "data",
XMLAGG(
XMLELEMENT(
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...

Henrik

LinkWithin

Related Posts with Thumbnails