如何从PostgreSQL的text类型字段XML中提取全部邮箱地址？

方案一：基于XML解析（推荐，更可靠） #

使用xpath结合unnest展开所有匹配的邮箱内容，过滤空值后拼接成逗号分隔的字符串：

SELECT 
    id,
    array_to_string(
        array(
            SELECT trim(unnest(xpath('//value/text()', xmlparse(content xml_data)))::text)
            WHERE trim(unnest(xpath('//value/text()', xmlparse(content xml_data)))::text) <> ''
        ), ','
    ) AS emails
FROM contacts;

或者用LATERAL JOIN实现更清晰的逻辑，同时处理空值场景：

SELECT 
    c.id,
    COALESCE(array_to_string(array_agg(v.email), ','), '') AS emails
FROM contacts c
LEFT JOIN LATERAL (
    SELECT trim(unnest(xpath('//value/text()', xmlparse(content c.xml_data)))::text) AS email
) v ON v.email <> ''
GROUP BY c.id;

说明：

• xpath('//value/text()', ...)提取所有<value>标签内的文本，返回XML节点数组
• unnest将数组展开为多行记录，方便后续过滤和聚合
• trim处理可能存在的空白字符，过滤空值或仅含空格的无效内容
• array_agg将多行邮箱聚合为数组，array_to_string转为逗号分隔格式
• COALESCE确保无有效邮箱时返回空字符串而非NULL

方案二：基于正则表达式（适合简单XML场景） #

如果XML结构无复杂嵌套，也可以用正则全局匹配所有邮箱后聚合：

SELECT 
    id,
    COALESCE(array_to_string(array_agg(trim(match[1])), ','), '') AS emails
FROM contacts c
LEFT JOIN LATERAL REGEXP_MATCHES(c.xml_data, '<value>([^<]+)</value>', 'g') m(match) ON true
WHERE trim(match[1]) <> ''
GROUP BY c.id;

说明：

• 正则'<value>([^<]+)</value>'匹配<value>标签内的非<>字符（即邮箱内容）
• 'g'标志开启全局匹配，返回所有符合条件的结果
• 同样通过trim过滤空白，array_agg和array_to_string完成拼接

原有语句失败原因 #

• 第一个XPath语句中[1]限定仅取第一个匹配结果，因此只能拿到单个邮箱
• 第二个正则语句缺少'g'全局匹配标志，REGEXP_MATCHES默认仅返回第一个匹配项

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