PL/SQL中XMLType数据提取咨询：新手参考Oracle示例实操求助

Hey there! Since you're new to PL/SQL and working with XMLType, let's break down how to extract data from your Xml_tab table based on the examples you shared. Your table has two distinct XML structures—one for employees (<EMP>) and one for purchase orders (<PO>)—so we'll cover practical approaches for both scenarios.

If you just need to pull individual elements from the XML, XMLQuery is a straightforward option. It lets you target specific nodes using XPath expressions.

For the employee records (<EMP>):

SELECT
  XMLQuery('/EMP/EMPNO/text()' PASSING xmlval RETURNING CONTENT) AS employee_id,
  XMLQuery('/EMP/ENAME/text()' PASSING xmlval RETURNING CONTENT) AS employee_name
FROM Xml_tab
-- Filter to only rows containing the EMP structure
WHERE XMLExists('/EMP' PASSING xmlval);

For the purchase order records (<PO>):

SELECT
  XMLQuery('/PO/PONO/text()' PASSING xmlval RETURNING CONTENT) AS po_number,
  XMLQuery('/PO/PONAME/text()' PASSING xmlval RETURNING CONTENT) AS po_name
FROM Xml_tab
WHERE XMLExists('/PO' PASSING xmlval);

The XMLExists check ensures we only process rows with the matching XML structure, avoiding errors from mismatched XPath queries.

If you want to treat the XML data like a standard relational table (with columns for each XML element), XMLTable is your best bet. It maps XML nodes directly to table columns, making it easy to join with other tables or run aggregate queries.

For employee data:

SELECT emp.empno, emp.ename
FROM Xml_tab,
     XMLTable('/EMP' PASSING xmlval
              COLUMNS
                empno NUMBER PATH 'EMPNO',
                ename VARCHAR2(50) PATH 'ENAME') emp
WHERE XMLExists('/EMP' PASSING xmlval);

For purchase order data:

SELECT po.pono, po.poname
FROM Xml_tab,
     XMLTable('/PO' PASSING xmlval
              COLUMNS
                pono NUMBER PATH 'PONO',
                poname VARCHAR2(50) PATH 'PONAME') po
WHERE XMLExists('/PO' PASSING xmlval);

Since your table has two different XML types, you can combine results using UNION ALL to see all extracted data in one output:

-- Extract employee records
SELECT
  'EMPLOYEE' AS record_type,
  emp.empno AS identifier,
  emp.ename AS name
FROM Xml_tab,
     XMLTable('/EMP' PASSING xmlval
              COLUMNS
                empno NUMBER PATH 'EMPNO',
                ename VARCHAR2(50) PATH 'ENAME') emp
UNION ALL
-- Extract purchase order records
SELECT
  'PURCHASE ORDER' AS record_type,
  po.pono AS identifier,
  po.poname AS name
FROM Xml_tab,
     XMLTable('/PO' PASSING xmlval
              COLUMNS
                pono NUMBER PATH 'PONO',
                poname VARCHAR2(50) PATH 'PONAME') po;

All these methods work with the table and data you created, so you can run them directly to see how they extract the XML content.

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