Question on workaround to parse_xml truncating zeros in the end

Question

As per snowflake documentation - If the content of an XML element is a number with digits after the decimal point, then PARSE_XML might truncate trailing zeros.

https://docs.snowflake.com/en/sql-reference/functions/parse_xml.html

We need to know how can we avoid this truncation, is there any workaround to this ?

Answer 1

so making some data to see this

select 
    parse_xml(column1) as v,
    GET(v, '@attr') as attr
  from values
    ('<a attr="123">text</a>'),
    ('<a attr="223.00">text</a>'),
    ('<a attr="323.30">text</a>'),
    ('<a attr="423.04">text</a>');

gives:

V	ATTR
text	123
text	223
text	323.3
text	423.04

so it's true, the 'zeros' are going, but to another degree they also where not there.

so adding another column that we just turn to number

select 
    parse_xml(column1) as v
    ,GET(v, '@attr')::float as attr
    ,column2::float 
  from values
    ('<a attr="123">text</a>',123),
    ('<a attr="223.00">text</a>',223.00),
    ('<a attr="323.30">text</a>',323.30),
    ('<a attr="423.04">text</a>',423.04 );

V	ATTR	COLUMN2::FLOAT
text	123	123
text	223	223
text	323.3	323.3
text	423.04	423.04

so it's not so much that the "zero's are truncated" as if it looks like a number, it's treated like a number, and floating point numbers don't need trailing zeros.

so the solution is to turn the number back to text, and put the zero's back to the desired level:

select 
    parse_xml(column1) as v
    ,GET(v, '@attr')::float as attr
    ,to_char(attr,'999.0000') as attr_char
    ,column2::float 
    ,to_char(column2::float ,'999.0000') as float_char
  from values
    ('<a attr="123">text</a>',123),
    ('<a attr="223.00">text</a>',223.00),
    ('<a attr="323.30">text</a>',323.30),
    ('<a attr="423.04">text</a>',423.04 );

gives:

V	ATTR	ATTR_CHAR	COLUMN2::FLOAT	FLOAT_CHAR
text	123	123.0000	123	123.0000
text	223	223.0000	223	223.0000
text	323.3	323.3000	323.3	323.3000
text	423.04	423.0400	423.04	423.0400