Db2 – The leading zeroes before decimal point is missing in string formatting of decimal data in XML in db2 11.1

db2db2-luw

When generating XML that includes decimal values in Db2 11.1, the leading zero right before the decimal point isremoved.

In the following query, the decimal value .00000000000000 is published even it should come as 0.00000000000000 from the XMLELEMENT function.

SELECT XMLElement(
  NAME "keytable",
  XMLAGG(
    XMLELEMENT(
      NAME "tab", 
      XMLAttributes( interp as "interp")
    )
  ) OPTION null ON NULL
) 
FROM abc where wkstn_oid_sh = 100 AND wkstn_oid_lng = 4211"

The correct result should be :

<KEYTABLE><TAB INTERP="0.00000000000000"/><TAB INTERP="0.00000000000000"/> </KEYTABLE>.

But currently db2 returns : 

<KEYTABLE><TAB INTERP=".00000000000000"/><TAB INTERP=".00000000000000"/></KEYTABLE>.

How can I get it to format decimals as 0.00000000000000?

Best Answer

If your consumer is so sensitive to the number format and cannot interpret values without leading zeroes, you can format your numeric values explicitly:

...xmlattributes(varchar_format(interp,'S00.00000000') as "interp")
Related Question