如何从Amazon Athena中提取XML列存储的数据？

当然可以！虽然Athena早期确实没有原生的XML解析支持，但现在基于Presto的版本已经提供了实用的XPATH相关函数，同时对于简单场景，你也可以用正则表达式来提取数据。下面给你两种可行的方案：

Athena目前支持xpath_string、xpath等函数，可以直接通过XPATH表达式提取XML中的指定内容，这是处理结构化XML最可靠的方式。

示例场景 #

假设你的表my_orders中有一列xml_payload，存储的XML结构如下：

<order>
  <order_id>12345</order_id>
  <customer>
    <name>John Doe</name>
    <email>john@example.com</email>
  </customer>
  <items>
    <item>Laptop</item>
    <item>Mouse</item>
  </items>
</order>

提取单节点值 #

使用xpath_string提取单个节点的文本内容：

SELECT
  xpath_string(xml_payload, '/order/order_id') AS order_id,
  xpath_string(xml_payload, '/order/customer/name') AS customer_name,
  xpath_string(xml_payload, '/order/customer/email') AS customer_email
FROM my_orders

提取多节点值 #

如果目标节点有多个实例（比如上面的<item>），可以用xpath函数返回数组，再结合数组函数处理：

SELECT
  xpath_string(xml_payload, '/order/order_id') AS order_id,
  -- 将多节点值转为逗号分隔的字符串
  array_join(xpath(xml_payload, '/order/items/item/text()'), ', ') AS item_list,
  -- 或者展开为多行
  unnest(xpath(xml_payload, '/order/items/item/text()')) AS item_name
FROM my_orders

注意事项 #

• 确保XML格式有效且结构规范，如果有缺失标签或语法错误，XPATH函数会返回NULL或报错；
• 如果XML中包含换行符或多余空格，可以先用replace(xml_payload, '\n', '')清理后再解析；
• XPATH表达式要匹配XML的实际层级，注意节点名称的大小写（XML是大小写敏感的）。

如果你的XML结构非常简单，没有复杂嵌套或属性，也可以用Athena的regexp_extract函数通过正则匹配提取内容。

示例查询 #

针对上面的XML结构，用正则提取核心字段：

SELECT
  regexp_extract(xml_payload, '<order_id>(.*?)</order_id>', 1) AS order_id,
  regexp_extract(xml_payload, '<name>(.*?)</name>', 1) AS customer_name,
  regexp_extract(xml_payload, '<email>(.*?)</email>', 1) AS customer_email
FROM my_orders

局限性提醒 #

• 正则表达式无法处理复杂的XML结构（比如带属性的节点、嵌套层级深的内容、标签大小写不固定等），容易出现匹配错误；
• 如果XML中存在转义字符（比如<代替<），正则匹配会失效，此时优先用XPATH方案。

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