使用PL/SQL提取XML属性值：拼接email节点的email_id为字符串

没问题，我来帮你搞定这个需求！你说得对，XMLTable确实是提取XML节点数据的核心工具，再搭配字符串聚合函数就能轻松得到你想要的逗号分隔格式字符串。

先看示例XML #

假设你的XML结构是这样的：

<root>
  <email>
    <email_id>user1@email.com</email_id>
    <name>User One</name>
  </email>
  <email>
    <email_id>user2@email.com</email_id>
    <name>User Two</name>
  </email>
  <email>
    <email_id>user3@email.com</email_id>
    <name>User Three</name>
  </email>
</root>

Oracle数据库实现方案 #

用XMLTable提取所有email_id，再用LISTAGG函数拼接：

WITH xml_data AS (
  -- 这里替换成你的XML数据来源，比如表中的XML列
  SELECT XMLTYPE('<root>
  <email>
    <email_id>user1@email.com</email_id>
    <name>User One</name>
  </email>
  <email>
    <email_id>user2@email.com</email_id>
    <name>User Two</name>
  </email>
  <email>
    <email_id>user3@email.com</email_id>
    <name>User Three</name>
  </email>
</root>') AS xml_doc
  FROM dual
)
SELECT LISTAGG(email_id, ', ') WITHIN GROUP (ORDER BY email_id) AS concatenated_emails
FROM xml_data,
     -- 用XMLTable解析每个email节点，提取email_id
     XMLTable('/root/email'
              PASSING xml_doc
              COLUMNS email_id VARCHAR2(100) PATH 'email_id');

PostgreSQL数据库实现方案 #

如果用PostgreSQL，语法类似，只是聚合函数换成string_agg：

WITH xml_data AS (
  SELECT '<root>
  <email>
    <email_id>user1@email.com</email_id>
    <name>User One</name>
  </email>
  <email>
    <email_id>user2@email.com</email_id>
    <name>User Two</name>
  </email>
  <email>
    <email_id>user3@email.com</email_id>
    <name>User Three</name>
  </email>
</root>'::xml AS xml_doc
)
SELECT string_agg(email_id, ', ') AS concatenated_emails
FROM xml_data,
     xmltable('/root/email'
              PASSING xml_doc
              COLUMNS email_id text PATH 'email_id');

关键步骤解释 #

• XMLTable解析：/root/email是XPath表达式，用来定位到每个<email>节点；PATH 'email_id'指定从每个<email>节点下提取<email_id>子节点的文本值。
• 字符串聚合：LISTAGG（Oracle）或string_agg（PostgreSQL）负责把多行的email_id拼接成单个字符串，, 是你要的分隔符，ORDER BY可以控制拼接的顺序（如果不需要顺序可以去掉）。
• 实际场景适配：如果你的XML存储在表的某个列里，只需要把CTE里的dual（Oracle）或硬编码XML替换成你的表名和XML列名即可。

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