How to port SQL Server OPENXML to Oracle PL/SQL

If you want to port code that contain OPENXML calls from SQL Server to Oracle, you will find that there is no such call. OPENXML works with sp_xml_preparedocument and xp_xml_removedocument calls to produce (among other things) a relational table based on XML document.



In my case, I use the XML document to pass an array of primary keys, use them as a filter for a table and join and return them.



For example suppose an application based on SQL Server TSQL has:

A table "BRANDS" contain a BRAND_ID (INT) and a TITLE (VARCHAR)

such as:

BRAND_ID TITLE

1 Nokia

2 Siemens

3 Other

4 IBM

5 HP



A stored procedure GET_BRAND_LIST as:

CREATE PROCEDURE dbo.GET_BRAND_LIST(@IDLIST NTEXT )

AS

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @IDLIST


SELECT BRAND_ID, TITLE FROM BRANDS

INNER JOIN OPENXML (@hDoc, '/s/k',2) WITH (I int) AS ResultSet

ON BRANDS.BRAND_ID = ResultSet.I



EXEC sp_xml_removedocument @hDoc

RETURN



GO



And an XML input similar to:


where:

• s is the root element




• k specifies a primary key and




• I the value of the primary key

such as:




calling GET_BRAND_LIST will return the brands with BRAND_ID equal to 1, 3 and 4.





Here is how to do the same in Oracle:

• Use CBLOB instead on NTEXT to pass the XML value.




• Use XMLType instead on sp_xml_preparedocument and sp_xml_removedocument calls.




• Use XMLSequence to convert and create a temporary table filled with the primary keys.




• Use EXTRACT to get the primary key value to perform the Join.

The complete Oracle code:
PROCEDURE GET_BRAND_LIST (cur_OUT OUT T_CURSOR, pIDLIST CLOB)
IS
anXmlType XmlType;
V_CURSOR T_CURSOR;
BEGIN
anXmlType := XmlType(pIDLIST);
OPEN V_CURSOR FOR
SELECT BRANDS.BRAND_ID, BRANDS.TITLE
FROM BRANDS,
table(XMLSequence(extract(anXMLType, '/s/k/*'))) ResultSet
WHERE BRANDS.BRAND_ID = TO_NUMBER(EXTRACT(value(ResultSet), 'I/text()'));



cur_OUT := V_CURSOR;
END;