Snowflake中XML解析：如何将员工文档类型提取至单个列？

假设你的表名为employee_xml_table，存储XML的Variant列名为xml_data，XML结构包含<employees>根节点、<employee>子节点，每个员工下有<emp_id>、<emp_type>以及<documents>下的多个<document>节点（含<doc_type>）。以下是实现目标的SQL方案：

方案1：将文档类型合并为逗号分隔的字符串 #

WITH employee_docs AS (
    -- 展开员工节点，提取基础信息
    SELECT
        XMLGET(emp.value, 'emp_id'):"$"::STRING AS emp_id,
        XMLGET(emp.value, 'emp_type'):"$"::STRING AS emp_type,
        -- 展开当前员工的所有文档节点，提取文档类型
        XMLGET(doc.value, 'doc_type'):"$"::STRING AS doc_type
    FROM employee_xml_table,
        FLATTEN(xml_data:"$", PATH => 'employees.employee') AS emp,
        FLATTEN(emp.value:"$", PATH => 'documents.document') AS doc
)
-- 按员工聚合，合并文档类型
SELECT
    emp_id,
    emp_type,
    LISTAGG(doc_type, ', ') WITHIN GROUP (ORDER BY doc_type) AS emp_docs
FROM employee_docs
GROUP BY emp_id, emp_type;

方案2：将文档类型合并为数组 #

如果需要保留数组格式，用ARRAY_AGG替代LISTAGG：

WITH employee_docs AS (
    SELECT
        XMLGET(emp.value, 'emp_id'):"$"::STRING AS emp_id,
        XMLGET(emp.value, 'emp_type'):"$"::STRING AS emp_type,
        XMLGET(doc.value, 'doc_type'):"$"::STRING AS doc_type
    FROM employee_xml_table,
        FLATTEN(xml_data:"$", PATH => 'employees.employee') AS emp,
        FLATTEN(emp.value:"$", PATH => 'documents.document') AS doc
)
SELECT
    emp_id,
    emp_type,
    ARRAY_AGG(doc_type ORDER BY doc_type) AS emp_docs
FROM employee_docs
GROUP BY emp_id, emp_type;

结构适配说明 #

如果你的XML节点名或层级不同，只需调整以下部分：

• FLATTEN中的PATH参数：比如根节点不是<employees>，就修改为对应路径
• XMLGET的节点名称：比如文档类型节点叫document_type，就把'doc_type'替换成'document_type'

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