如何在Snowflake中查询嵌套XML数据并将标签替换为逗号

示例数据 #

<TAGS>
  <TAG>
    <TAG_ID>19</TAG_ID>
    <NAME>Ct</NAME>
  </TAG>
  <TAG>
    <TAG_ID>104</TAG_ID>
    <NAME>Ntt</NAME>
  </TAG>
</TAGS>

期望输出 #

19,Ct,104,Ntt

解决方案（以SQL Server为例） #

利用XML类型的节点查询和字符串聚合函数，精准提取所有非空文本节点并拼接成目标格式：

1. 定义测试XML变量 #

DECLARE @xml XML = N'<TAGS>
  <TAG>
    <TAG_ID>19</TAG_ID>
    <NAME>Ct</NAME>
  </TAG>
  <TAG>
    <TAG_ID>104</TAG_ID>
    <NAME>Ntt</NAME>
  </TAG>
</TAGS>'

2. 提取文本并拼接 #

SELECT STRING_AGG(LTRIM(RTRIM(t.value('.', 'NVARCHAR(MAX)'))), ',') AS Result
FROM @xml.nodes('//text()') AS x(t)
WHERE LTRIM(RTRIM(t.value('.', 'NVARCHAR(MAX)'))) <> ''

逻辑说明 #

• //text()：选中XML结构中所有层级的文本节点（包括标签内的内容）
• LTRIM(RTRIM(...))：去除文本节点中的换行、空格等空白字符
• WHERE子句：过滤掉仅含空白的无效文本节点
• STRING_AGG(...)：将所有有效文本用逗号连接成单一字符串

解决方案（以MySQL为例） #

若使用MySQL，可结合ExtractValue和GROUP_CONCAT实现：

SET @xml = '<TAGS>
  <TAG>
    <TAG_ID>19</TAG_ID>
    <NAME>Ct</NAME>
  </TAG>
  <TAG>
    <TAG_ID>104</TAG_ID>
    <NAME>Ntt</NAME>
  </TAG>
</TAGS>';

SELECT GROUP_CONCAT(value SEPARATOR ',') AS Result
FROM (
  SELECT ExtractValue(@xml, '//TAG_ID[1]') AS value UNION ALL
  SELECT ExtractValue(@xml, '//NAME[1]') UNION ALL
  SELECT ExtractValue(@xml, '//TAG_ID[2]') UNION ALL
  SELECT ExtractValue(@xml, '//NAME[2]')
) AS temp;

注：此方法需明确TAG节点的数量，若节点数量不固定，需结合递归查询处理。

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