Q:

How to Query an XML-containing Clob for nodes with PL-SQL

In T-SQL I'm doing this:
CREATE PROCEDURE [dbo].[ps_GetAuditUpdatesRequired]
@AuditXML xml
AS
SET NOCOUNT ON

SELECT 
    doc.col.value('@id', 'int') AS id
FROM  
    @AuditXML.nodes('/rths/rth') AS doc(col)
WHERE (
  -- Get anything needs to be deleted and does exist in the database already
  doc.col.value('@method', 'nvarchar(16)') = 'DELETE' AND EXISTS ( SELECT 1 FROM audits_ WHERE doc.col.value('@id', 'int') = audits_.Id_RetinaId ) OR
  -- Get anything that is not set to be deleted and does not already exist with a newer version
  doc.col.value('@method', 'nvarchar(16)') != 'DELETE' AND NOT EXISTS ( SELECT 1 FROM audits_ WHERE doc.col.value('@id', 'int') = audits_.Id_RetinaId AND doc.col.value('@versionSerial', 'int' ) <= audits_.nl_VersionSerial)
  )

SET NOCOUNT OFF

How can I do this in oracle?
Note: The AuditXML parameter will be a CLOB in the Oracle version.

A:

If I understand what you're doing you need an XMLTable to extract the node attributes as relational data; you can then use the columns generated from that in your where clause:
SELECT doc.id
FROM XMLTable('/rths/rth'
  PASSING XMLType(:auditXML)
  COLUMNS id NUMBER PATH '@id',
    method NVARCHAR2(16) PATH '@method',
    versionSerial NUMBER PATH '@versionSerial'
) doc
WHERE (
  -- Get anything needs to be deleted and does exist in the database already
  doc.method = 'DELETE' AND EXISTS (
    SELECT 1
    FROM audits_
    WHERE doc.id = audits_.Id_RetinaId
)
OR (
  -- Get anything that is not set to be deleted and does not already exist with a newer version
  doc.method != 'DELETE' AND NOT EXISTS (
    SELECT 1
    FROM audits_
    WHERE doc.id = audits_.Id_RetinaId
    AND doc.versionSerial <= audits_.nl_VersionSerial)
  )
)

I've use :auditXML as a bind variable, but you can pass in a table column or a string literal - though the latter is limited to 4K in plain SQL (up to 12c).
An Oracle procedure can't return the result of a query quite you can in T-SQL. In a procedure the query would have to select into something, but depending on how you're using it, you might actually want a pipelined table function and a cursor loop to pipe the rows out, or a function that returns a ref cursor (or a procedure that has a ref cursor OUT parameter).

