How to run SQL and return nice XML in DB2

Question

I am trying to make a SQL query in a way that it returns nice XML. I was able to do something like this:

Select REC2XML(1.0, 'COLATTVAL_XML', '', A, B, C) From (select A, B, C from MY_TABLE where X = '1');

However, the result looks like this

<row>
<column name="A">1</column>
<column name="B">2</column>
<column name="C">3</column>
</row>

While we would like it to have the actual column names as the element name, something like this:

<row>
<A>1</A>
<B>2</B>
<C>3</C>
</row>

Any ideas?

Stavr00 · Accepted Answer · 2016-05-26 20:30:33Z

2

Might as well rewrite with XMLELEMENT:

Select 
  XMLELEMENT(NAME row,XMLCONCAT(XMLELEMENT(NAME A,A),XMLELEMENT(NAME B,B),XMLELEMENT(NAME C,C)))
from MY_TABLE where X = '1';

answered May 26, 2016 at 20:30

Stavr00

3,3562 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, you answer was the hint to find a full solution with XMLSERIALZE as provided here: itjungle.com/fhg/fhg080812-story02.html