Snowflake中通过属性名与值调用XMLGET函数从XML提取数据

实现方案 #

你之前通过索引提取的方式依赖book节点的固定顺序和数量，只要任意catalog节点下的book出现缺失、顺序变化，结果就会错位。改用属性匹配+行转列的方式可以实现需求，完整查询如下：

WITH book_details AS (
    SELECT
        -- 用METADATA$ROW_ID唯一标识每一行原始XML记录（对应每个catalog节点）
        T1.METADATA$ROW_ID AS row_id,
        -- 提取book节点的id属性值
        book_nodes.value:"@id"::VARCHAR AS book_id,
        -- 提取book节点的文本内容
        book_nodes.value:"$"::VARCHAR(100) AS book_content
    FROM BooksXML T1,
    -- 展开<Books>节点下的所有子节点
    LATERAL FLATTEN(input => XMLGET(T1.xml, 'Books'):"$") AS book_nodes
    -- 过滤只保留book类型的节点
    WHERE book_nodes.value:"@" = 'book'
)
-- 行转列得到最终宽表
SELECT
    MAX(CASE WHEN book_id = 'bk101' THEN book_content END) AS BK101,
    MAX(CASE WHEN book_id = 'bk102' THEN book_content END) AS BK102,
    MAX(CASE WHEN book_id = 'bk103' THEN book_content END) AS BK103,
    MAX(CASE WHEN book_id = 'bk104' THEN book_content END) AS BK104,
    MAX(CASE WHEN book_id = 'bk105' THEN book_content END) AS BK105
FROM book_details
GROUP BY row_id
ORDER BY row_id;

逻辑说明 #

• 用LATERAL FLATTEN将每个节点下的所有子节点拆分为单独的行，避免依赖固定索引
• 通过book_nodes.value:"@id"直接匹配id属性值，不受节点顺序、数量影响
• 用CASE WHEN + MAX聚合实现行转列，没有对应id的位置自动返回NULL，完全匹配你给出的预期结果

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