如何从XMLTYPE提取并打印所有供应商站点ERPSITEID值？

我来帮你搞定这个XML提取的问题！你的需求是从给定的XMLTYPE里提取所有<ERPSITEID>的值，不管站点数量是2个、3个还是更多，下面给你两种实用的PL/SQL实现方式：

方法一：用XMLTable+游标遍历 #

这种方式直观易懂，适合逐个处理每个站点ID：

DECLARE
  vendor_xml xmltype := xmltype( '<VENDORLIST> <VENDOR> <VENDOR_NAME>Testvendorname</VENDOR_NAME> <SITE> <ERPSITEID>1254</ERPSITEID> </SITE> <SITE> <ERPSITEID>90</ERPSITEID> </SITE> </VENDOR> </VENDORLIST>');
  vendor_site NUMBER(5);
BEGIN
  -- 通过XMLTable把XML中的SITE节点转换成关系型行数据
  FOR rec IN (
    SELECT erpsiteid
    FROM XMLTable(
      -- XPath定位到所有SITE节点
      '/VENDORLIST/VENDOR/SITE'
      PASSING vendor_xml
      -- 定义要提取的列：从ERPSITEID节点取值，类型为NUMBER(5)
      COLUMNS erpsiteid NUMBER(5) PATH 'ERPSITEID'
    )
  ) LOOP
    vendor_site := rec.erpsiteid;
    DBMS_OUTPUT.PUT_LINE('供应商站点ID: ' || vendor_site);
  END LOOP;
END;
/

运行这段代码后，会输出：

供应商站点ID: 1254
供应商站点ID: 90

方法二：批量收集到集合中处理 #

如果站点数量较多，用批量收集的方式效率更高：

DECLARE
  vendor_xml xmltype := xmltype( '<VENDORLIST> <VENDOR> <VENDOR_NAME>Testvendorname</VENDOR_NAME> <SITE> <ERPSITEID>1254</ERPSITEID> </SITE> <SITE> <ERPSITEID>90</ERPSITEID> </SITE> </VENDOR> </VENDORLIST>');
  -- 定义存储站点ID的集合类型
  TYPE site_ids_t IS TABLE OF NUMBER(5);
  site_ids site_ids_t;
BEGIN
  -- 批量提取所有站点ID到集合中
  SELECT erpsiteid
  BULK COLLECT INTO site_ids
  FROM XMLTable(
    '/VENDORLIST/VENDOR/SITE'
    PASSING vendor_xml
    COLUMNS erpsiteid NUMBER(5) PATH 'ERPSITEID'
  );
  
  -- 遍历集合输出所有站点ID
  IF site_ids.COUNT > 0 THEN
    FOR i IN site_ids.FIRST..site_ids.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('供应商站点ID: ' || site_ids(i));
    END LOOP;
  END IF;
END;
/

关键说明 #

• XMLTable是Oracle处理XML的强大工具，它能把XML中的重复节点（比如这里的多个<SITE>）转换成行数据，完美适配任意数量的站点场景。
• XPath表达式/VENDORLIST/VENDOR/SITE精准定位到所有站点节点，确保不会遗漏任何一个<ERPSITEID>。

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