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:
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:
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;
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;
