2007-09-13

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;


4 comments:

Anonymous said...

great post :)

Anonymous said...

Hi Chris, this is an excellent article, and thank you for it! I want to know if its possible to use this approach to insert into Oracle via a Procedure. I have the same scenario setup for SQL Server 2005 and it works perfectly! I am hoping to achieve the same with Oracle.

thank u!

Rayna said...

Exactly what I was looking for, thanks a mil for this informative post!

Anonymous said...

Very useful