Snowflake解析含键值对的嵌套XML：单个KVP异常问题

问题描述 #

在Snowflake中解析包含键值对的嵌套XML时，处理多个<Kvp>节点的文档（如docId=1、2）可正常提取数据，但处理单个<Kvp>节点的文档（docId=3）时，会生成多条null值记录，无法正确获取k6-v6键值对。

示例XML与查询代码 #

WITH xml_table
AS (
    SELECT 1 AS ID
        , PARSE_XML(
            '<root>
                <docs>
                    <doc>
                        <Id>1</Id>
                        <Name>
                            <Kvp>
                                <Key>k1</Key>
                                <Value>v1</Value>
                            </Kvp>
                            <Kvp>
                                <Key>k2</Key>
                                <Value>v2</Value>
                            </Kvp>
                            <Kvp>
                                <Key>k3</Key>
                                <Value>v3</Value>
                            </Kvp>
                        </Name>
                    </doc>
                    <doc>
                        <Id>2</Id>
                        <Name>
                            <Kvp>
                                <Key>k4</Key>
                                <Value>v4</Value>
                            </Kvp>
                            <Kvp>
                                <Key>k5</Key>
                                <Value>v5</Value>
                            </Kvp>
                        </Name>
                    </doc>
                    <doc>
                        <Id>3</Id>
                        <Name>
                            <Kvp>
                                <Key>k6</Key>
                                <Value>v6</Value>
                            </Kvp>
                        </Name>
                    </doc>
                </docs>
            </root>'
        ) AS XML_COL
    )
SELECT docs.ID
    , docs.docId
    , GET(XMLGET(kvps.VALUE, 'Key'), '$')::STRING AS docNameKey
    , GET(XMLGET(kvps.VALUE, 'Value'), '$')::STRING AS docNameValue
FROM (
    SELECT xml_table.ID
        , xml_table.XML_COL
        , GET(xml_table.XML_COL, '@')::STRING AS ROOT_NODE_NAME
        , XMLGET(docs.VALUE, 'Id') : "$"::STRING AS docId
        , XMLGET(docs.VALUE, 'Name') AS docName
    FROM xml_table
        , LATERAL FLATTEN(GET(XMLGET(xml_table.XML_COL, 'docs'), '$')) AS docs
    WHERE 1 = 1
) docs
, LATERAL FLATTEN(GET(docs.docName, '$')) AS kvps
WHERE 1 = 1

现有查询结果 #

ID  DOCID   DOCNAMEKEY  DOCNAMEVALUE
1   1       k1          v1
1   1       k2          v2
1   1       k3          v3
1   2       k4          v4
1   2       k5          v5
1   3       null        null
1   3       null        null
1   3       null        null
1   3       null        null

期望输出 #

ID  DOCID   DOCNAMEKEY  DOCNAMEVALUE
1   1       k1          v1
1   1       k2          v2
1   1       k3          v3
1   2       k4          v4
1   2       k5          v5
1   3       k6          v6

异常原因分析 #

核心问题在于Snowflake对单节点和多节点XML的解析返回类型不同：

• 当<Name>下存在多个<Kvp>节点时，GET(docs.docName, '$')返回的是数组类型，LATERAL FLATTEN可以正确展开每个<Kvp>节点，提取对应的Key和Value。
• 当<Name>下只有一个<Kvp>节点时，GET(docs.docName, '$')返回的是单个XML对象而非数组，此时FLATTEN会将该对象的内部属性（如Key、Value等）作为元素展开，而这些属性并非<Kvp>节点，因此无法获取有效数据，最终生成多条null记录。

解决方案 #

使用ARRAY_CONSTRUCT_COMPACT函数将单个XML对象转换为单元素数组，确保FLATTEN始终处理数组类型的数据，统一单节点和多节点的解析逻辑。

修改后的查询代码如下：

WITH xml_table
AS (
    SELECT 1 AS ID
        , PARSE_XML(
            '<root>
                <docs>
                    <doc>
                        <Id>1</Id>
                        <Name>
                            <Kvp>
                                <Key>k1</Key>
                                <Value>v1</Value>
                            </Kvp>
                            <Kvp>
                                <Key>k2</Key>
                                <Value>v2</Value>
                            </Kvp>
                            <Kvp>
                                <Key>k3</Key>
                                <Value>v3</Value>
                            </Kvp>
                        </Name>
                    </doc>
                    <doc>
                        <Id>2</Id>
                        <Name>
                            <Kvp>
                                <Key>k4</Key>
                                <Value>v4</Value>
                            </Kvp>
                            <Kvp>
                                <Key>k5</Key>
                                <Value>v5</Value>
                            </Kvp>
                        </Name>
                    </doc>
                    <doc>
                        <Id>3</Id>
                        <Name>
                            <Kvp>
                                <Key>k6</Key>
                                <Value>v6</Value>
                            </Kvp>
                        </Name>
                    </doc>
                </docs>
            </root>'
        ) AS XML_COL
    )
SELECT docs.ID
    , docs.docId
    , GET(XMLGET(kvps.VALUE, 'Key'), '$')::STRING AS docNameKey
    , GET(XMLGET(kvps.VALUE, 'Value'), '$')::STRING AS docNameValue
FROM (
    SELECT xml_table.ID
        , xml_table.XML_COL
        , GET(xml_table.XML_COL, '@')::STRING AS ROOT_NODE_NAME
        , XMLGET(docs.VALUE, 'Id') : "$"::STRING AS docId
        , XMLGET(docs.VALUE, 'Name') AS docName
    FROM xml_table
        , LATERAL FLATTEN(GET(XMLGET(xml_table.XML_COL, 'docs'), '$')) AS docs
    WHERE 1 = 1
) docs
, LATERAL FLATTEN(ARRAY_CONSTRUCT_COMPACT(GET(docs.docName, '$'))) AS kvps
WHERE 1 = 1

修改后执行查询，即可得到符合期望的输出结果。

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