Using xmlserialize in db2 with a timestamp

Question

I was looking for a way to combine multiple returned rows into a single row on a db2 database (I have an application that can query a database, but will only work if a single row is returned). I found this solution which worked pretty well and was a lot easier than using recursive SQL. However, I ran into a problem when I tried to include a column that was set as TIMESTAMP instead of VARCHAR.

So how can I make this work if a column is a TIMESTAMP type?

Error:

SQL0440N  No authorized routine named "XMLTEXT" of type "FUNCTION" having 
compatible arguments was found.  SQLSTATE=42884

SQL0440N  No authorized routine named "XMLTEXT" of type "FUNCTION                                                      " having compatible arguments was found.
                           ".

Example:

select xmlserialize(
  xmlagg(
    xmlconcat(
      xmltext(column_name),
      xmltext(':'),
      xmltext(content),
      xmltext(','),
      xmltext(DATETIMESTAMP),
      xmltext(',')
    )
  ) as varchar(10000)
) 
from 
  yourtable

May be you can explicitly convert your timestamp to the VARCHAR data type, e.g. by using the built-in function like TIMESTAMP_FORMAT()? — mustaccio
I played with that a little but couldn't come up with a good solution. I made a less elegant solution that works for now, although a better solution would be nice. — Pryach
xmltext(CAST(MONTH(DATETIMESTAMP) AS VARCHAR(2))), xmltext('-'), xmltext(CAST(DAY(DATETIMESTAMP) AS VARCHAR(2))), xmltext('-'), xmltext(CAST(YEAR(DATETIMESTAMP) AS VARCHAR(4))), xmltext(' '), xmltext(CAST(HOUR(DATETIMESTAMP) AS VARCHAR(2))), xmltext(':'), xmltext(CAST(MINUTE(DATETIMESTAMP) AS VARCHAR(2))), xmltext(':'), xmltext(CAST(SECOND(DATETIMESTAMP) AS VARCHAR(2))), — Pryach

data_henrik data_henrik · Accepted Answer · 2015-08-28T05:53:17

Instead of the suggested CAST you could wrap the TOCHAR` function around the timestamp value:

select xmlserialize( xmlagg( xmlconcat( xmltext(column_name), xmltext(':'), xmltext(content), xmltext(','), xmltext(TO_CHAR(DATETIMESTAMP)), xmltext(',') ) ) as varchar(10000) ) from yourtable

If you are on a recent version of DB2 and have LISTAGG available I would recommend to use that function. It is much faster than converting the SQL input to XML types and then converting it back. It requires some CPU cycles due to all the official rules involved.

Using xmlserialize in db2 with a timestamp

1 Answers