向Snowflake加载含XML类型数据触发SQL编译错误求助

报错原因 #

Snowflake原生XML文件格式默认单次加载仅能输出1个VARIANT/OBJECT/ARRAY类型的列，直接执行COPY INTO语句往包含3个字段的CUSTOMER表插入数据，不符合XML加载的默认规则，因此触发报错：

002019 (0A000): SQL compilation error:
XML file format can produce one and only one column of type variant or object or array. Use CSV file format if you want to load more than one column.

解决方案 #

方案1：中转表分步加载（适合大数据量场景，便于排查解析问题） #

• 第一步：创建临时XML中转表，仅存储完整XML内容的单列

CREATE OR REPLACE TEMP TABLE XML_STAGING (XML_CONTENT VARIANT);

• 第二步：将XML文件加载到中转表

COPY INTO XML_STAGING 
FROM @my_stage_name/customer.xml 
FILE_FORMAT = (TYPE = XML);

• 第三步：解析中转表的XML内容，拆分字段插入目标表

INSERT INTO "SANDY"."CUSTOMER" ("CID", "INFO", "HISTORY")
SELECT
    -- 替换XML_GET中的节点名为你实际XML文件中的对应节点名
    XMLGET(XML_CONTENT, 'CID'):"$"::BIGINT AS CID,
    XMLGET(XML_CONTENT, 'Info') AS INFO,
    XMLGET(XML_CONTENT, 'History') AS HISTORY
FROM XML_STAGING;

方案2：COPY时直接解析（适合小数据量场景，无需创建额外表） #

直接在COPY语句中嵌套查询，解析XML内容后对应到目标表的三个字段：

COPY INTO "SANDY"."CUSTOMER" ("CID", "INFO", "HISTORY")
FROM (
    SELECT
        XMLGET($1, 'CID'):"$"::BIGINT,
        XMLGET($1, 'Info'),
        XMLGET($1, 'History')
    FROM @my_stage_name/customer.xml
)
FILE_FORMAT = (TYPE = XML);

注意事项 #

• XML节点名称大小写敏感，解析时的节点名需要和实际XML文件中的拼写完全一致
• 如果XML包含深层嵌套结构，可以使用:操作符搭配XPATH路径直接提取深层节点值
• 如果你的XML文件包含多行独立业务节点，可以在FILE_FORMAT中添加STRIP_OUTER_ELEMENT = TRUE配置，去掉外层根节点包裹，直接解析业务子节点。

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