如何在Snowflake中查询嵌套XML并实现全字段扁平化展开

核心问题原因 #

单字段返回正常、其余字段全为空的问题，基本都是XML解析路径写法错误、未按层级拆分节点导致的。Snowflake的XML解析不会自动跨层级匹配节点名，必须严格按照嵌套层级逐层提取。

可直接运行的查询示例 #

以下代码完全匹配你提供的XML结构（根节点pnet_get_performx_data_packet、多层级pxreport/px_params/reportdata节点），直接替换成你自己的XML表即可使用：

-- 构造测试临时表（和你描述的XML结构完全对齐，正式使用时替换为你自己的XML存储表即可）
WITH sample_xml AS (
    SELECT
        PARSE_XML('
        <pnet_get_performx_data_packet>
            <pxreport>
                <px_params>
                    <vehicle_number>粤B12345</vehicle_number>
                    <report_id>R001</report_id>
                    <collect_time>2024-05-01 10:00:00</collect_time>
                </px_params>
                <reportdata>
                    <metric_id>M001</metric_id>
                    <metric_value>12.5</metric_value>
                    <metric_name>车速</metric_name>
                </reportdata>
                <reportdata>
                    <metric_id>M002</metric_id>
                    <metric_value>1500</metric_value>
                    <metric_name>转速</metric_name>
                </reportdata>
            </pxreport>
            <pxreport>
                <px_params>
                    <vehicle_number>粤A67890</vehicle_number>
                    <report_id>R002</report_id>
                    <collect_time>2024-05-01 11:00:00</collect_time>
                </px_params>
                <reportdata>
                    <metric_id>M001</metric_id>
                    <metric_value>32.1</metric_value>
                    <metric_name>车速</metric_name>
                </reportdata>
            </pxreport>
        </pnet_get_performx_data_packet>
        ') AS xml_content
),
-- 打平所有pxreport节点，生成唯一序列标识避免后续关联串数据
flatten_pxreport AS (
    SELECT
        INDEX AS pxreport_seq,
        VALUE AS pxreport_node
    FROM sample_xml,
    LATERAL FLATTEN(XMLGET(xml_content, 'pxreport'))
),
-- 提取每个pxreport下的px_params公共参数
extract_px_params AS (
    SELECT
        pxreport_seq,
        XMLGET(px_params_node, 'vehicle_number'):"$"::STRING AS vehicle_number,
        XMLGET(px_params_node, 'report_id'):"$"::STRING AS report_id,
        XMLGET(px_params_node, 'collect_time'):"$"::TIMESTAMP AS collect_time
        -- 其余px_params下的字段按上述格式追加即可，节点名和XML内保持一致
    FROM (
        SELECT
            pxreport_seq,
            VALUE AS px_params_node
        FROM flatten_pxreport,
        LATERAL FLATTEN(pxreport_node:"$")
        WHERE VALUE:"@" = 'px_params'
    )
),
-- 打平每个pxreport下的所有reportdata业务记录
extract_reportdata AS (
    SELECT
        pxreport_seq,
        XMLGET(reportdata_node, 'metric_id'):"$"::STRING AS metric_id,
        XMLGET(reportdata_node, 'metric_value'):"$"::FLOAT AS metric_value,
        XMLGET(reportdata_node, 'metric_name'):"$"::STRING AS metric_name
        -- 其余reportdata下的字段按上述格式追加即可，节点名和XML内保持一致
    FROM (
        SELECT
            pxreport_seq,
            VALUE AS reportdata_node
        FROM flatten_pxreport,
        LATERAL FLATTEN(pxreport_node:"$")
        WHERE VALUE:"@" = 'reportdata'
    )
)
-- 关联公共参数和业务数据，输出完全扁平化的结果，可直接用于入库
SELECT
    p.vehicle_number,
    p.report_id,
    p.collect_time,
    d.metric_id,
    d.metric_value,
    d.metric_name
FROM extract_px_params p
JOIN extract_reportdata d
ON p.pxreport_seq = d.pxreport_seq;

自定义调整说明 #

• 正式使用时，将CTE中的sample_xml替换为你自己存储XML数据的业务表，xml_content替换为表中XML类型的字段名即可
• 新增字段时严格遵循XMLGET(直接上级节点, 'XML节点名'):"$"::对应数据类型的写法：如果是节点包裹的文本值就用"$"取值，如果是节点上的属性（格式为<节点名 属性名="属性值">）就用"@属性名"取值
• 提取节点时按节点名过滤的逻辑不要删除，否则会把不同类型的子节点混在一起导致取值错位
• 如果XML带命名空间前缀（比如<ns:pxreport>格式），XMLGET里写节点名时带上前缀即可，也可以用*:节点名的格式通配所有命名空间

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