如何在Snowflake原生SQL中将XML列转为JSON？相关疑问

1. MY_XML列显示为object是否正常？ #

正常。如果你的MY_XML列数据类型是VARIANT，Snowflake会自动将存储的XML字符串解析为XML对象（属于VARIANT的子类型），在查询结果中会显示为object。如果列是VARCHAR类型却显示为object，大概率是之前的查询中用PARSE_XML解析过，结果被缓存或转换为了VARIANT类型。

2. 转JSON后出现$/@键的问题及解决方法 #

你直接用TO_JSON(MY_XML)得到的是Snowflake内部XML对象的JSON表示——它用$标记元素文本内容，@标记属性，所以看不到业务字段名。要生成带业务键的JSON，得先提取XML中的元素和属性，再构造JSON：

方法1：用XML函数提取后构造JSON #

适用于结构固定的XML：

SELECT
  MY_XML,
  OBJECT_CONSTRUCT(
    '业务字段1', XMLTEXT(XMLGET(PARSE_XML(MY_XML), '元素名1')),
    '业务字段2', XMLATTR(XMLGET(PARSE_XML(MY_XML), '元素名2'), '属性名'),
    '业务字段3', XMLTEXT(XMLGET(XMLGET(PARSE_XML(MY_XML), '父元素'), '子元素'))
  ) AS business_json
FROM my_table;

方法2：用XPath提取后构造JSON #

适合复杂嵌套XML：

SELECT
  MY_XML,
  OBJECT_CONSTRUCT(
    '用户ID', XPATH_STRING(PARSE_XML(MY_XML), '/user/@id'),
    '用户名', XPATH_STRING(PARSE_XML(MY_XML), '/user/name'),
    '订单金额', XPATH_STRING(PARSE_XML(MY_XML), '/user/orders/order[1]/amount')
  ) AS business_json
FROM my_table;

3. 嵌套对象是否需要扁平化？ #

取决于你的使用场景：

• 如果后续要频繁用SQL查询单个字段，扁平化后查询更简便，无需多层嵌套解析；
• 如果需要保留原始嵌套结构给下游系统（如数据湖、API），则保留嵌套JSON更合适。

Snowflake可以用递归CTE结合FLATTEN实现XML扁平化，示例：

WITH RECURSIVE xml_flatten AS (
  SELECT
    主键字段,
    PARSE_XML(MY_XML) AS xml_node,
    XPATH_STRING(PARSE_XML(MY_XML), '/*/@id') AS root_id,
    0 AS depth
  FROM my_table
  UNION ALL
  SELECT
    f.主键字段,
    XMLGET(f.xml_node, 'child'),
    XPATH_STRING(XMLGET(f.xml_node, 'child'), '@child_id'),
    f.depth + 1
  FROM xml_flatten f
  WHERE XMLGET(f.xml_node, 'child') IS NOT NULL
)
SELECT * FROM xml_flatten;

4. 有没有类似json_extract_path_text的XML函数？ #

有，XPATH_STRING函数完全满足需求——它支持用XPath表达式直接提取XML中的文本或属性值，用法和json_extract_path_text类似：

-- 提取元素文本
SELECT XPATH_STRING(PARSE_XML(MY_XML), '/root/parent/child') AS child_text FROM my_table;

-- 提取属性值
SELECT XPATH_STRING(PARSE_XML(MY_XML), '/root/parent/@attr_name') AS attr_value FROM my_table;

另外，XMLGET+XMLTEXT/XMLATTR的组合也能实现类似效果，适合简单结构的XML。

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