使用SQL工作表从XML文件取数时推荐ID末尾0丢失问题求助

问题背景 #

从XML文件中检索推荐ID时，形如123.10的ID被自动转换为123.1，末尾的0丢失；其余如123.11这类ID保存正常。当前SQL中尝试将RRTRECRecommendationIDUDF字段转为VARCHAR，但问题根源是XML解析阶段该值已被识别为数字类型，导致精度丢失。

解决方法 #

• 强制以字符串类型读取节点内容：跳过默认的数字转换步骤，直接提取节点的原始字符串值。Snowflake中可通过TO_VARCHAR直接转换XML节点的提取结果，避免先转为数字。
• 使用XMLTEXT函数提取原始文本：XMLTEXT可直接获取XML节点的文本内容，不受节点类型影响，能保留原始格式。
• 通过GET_PATH指定字符串解析：若XML节点被定义为数值类型，可结合PARSE_XML和GET_PATH强制以字符串类型提取。

修改后的SQL示例 #

方法1：直接转换为字符串 #

SELECT 
        IFNULL(XMLGET(`rcafile.value`, 'PK'):"$"::NUMBER,0) AS RRTID,
        IFNULL(XMLGET(`actionplans.value`, 'PK'):"$"::NUMBER,0) AS SIAR_ID,
        IFNULL(XMLGET(`actionplans.value`, 'RcaID'):"$"::NUMBER,0),
        -- 关键修改：直接将节点值转为字符串，避免数字转换丢失末尾0
        IFNULL(TO_VARCHAR(XMLGET(`actionplans.value`, 'RRTRECRecommendationIDUDF'):"$"), '') AS RRTRECRecommendationIDUDF
FROM {database}.{schema}.{table},
        LATERAL FLATTEN(XMLGET({database}.{schema}.{Table}.xmldata,'CASES'):"$") rcafile, 
        LATERAL FLATTEN(rcafile.value:"$") rcacase,
        LATERAL FLATTEN(rcacase.value:"$", RECURSIVE => TRUE) actionplans,
    WHERE 
        GET(rcafile.value,'@') = 'RCAFile' AND
        GET(rcacase.value,'@') = 'RCACase' AND
        GET(actionplans.value,'@') = 'RCAActionPlans'

方法2：使用XMLTEXT提取原始文本 #

SELECT 
        IFNULL(XMLGET(`rcafile.value`, 'PK'):"$"::NUMBER,0) AS RRTID,
        IFNULL(XMLGET(`actionplans.value`, 'PK'):"$"::NUMBER,0) AS SIAR_ID,
        IFNULL(XMLGET(`actionplans.value`, 'RcaID'):"$"::NUMBER,0),
        -- 使用XMLTEXT获取节点原始文本内容
        IFNULL(XMLTEXT(XMLGET(`actionplans.value`, 'RRTRECRecommendationIDUDF')), '') AS RRTRECRecommendationIDUDF
FROM {database}.{schema}.{table},
        LATERAL FLATTEN(XMLGET({database}.{schema}.{Table}.xmldata,'CASES'):"$") rcafile, 
        LATERAL FLATTEN(rcafile.value:"$") rcacase,
        LATERAL FLATTEN(rcacase.value:"$", RECURSIVE => TRUE) actionplans,
    WHERE 
        GET(rcafile.value,'@') = 'RCAFile' AND
        GET(rcacase.value,'@') = 'RCACase' AND
        GET(actionplans.value,'@') = 'RCAActionPlans'

方法3：结合PARSE_XML和GET_PATH #

SELECT 
        IFNULL(XMLGET(`rcafile.value`, 'PK'):"$"::NUMBER,0) AS RRTID,
        IFNULL(XMLGET(`actionplans.value`, 'PK'):"$"::NUMBER,0) AS SIAR_ID,
        IFNULL(XMLGET(`actionplans.value`, 'RcaID'):"$"::NUMBER,0),
        -- 强制以字符串类型解析节点值
        IFNULL(GET_PATH(PARSE_XML(actionplans.value), '$.RRTRECRecommendationIDUDF')::STRING, '') AS RRTRECRecommendationIDUDF
FROM {database}.{schema}.{table},
        LATERAL FLATTEN(XMLGET({database}.{schema}.{Table}.xmldata,'CASES'):"$") rcafile, 
        LATERAL FLATTEN(rcafile.value:"$") rcacase,
        LATERAL FLATTEN(rcacase.value:"$", RECURSIVE => TRUE) actionplans,
    WHERE 
        GET(rcafile.value,'@') = 'RCAFile' AND
        GET(rcacase.value,'@') = 'RCACase' AND
        GET(actionplans.value,'@') = 'RCAActionPlans'

内容的提问来源于stack exchange，提问作者Ross H