xml conversion in pl/sql

From: Shishir <shishir456_at_gmail.com>
Date: Mon, 02 Jul 2007 12:57:24 -0700
Message-ID: <1183406244.396193.131960@o11g2000prd.googlegroups.com>

hi ,

How do i map the xml handling code in t-sql to pl/sql?? for example , here is the t-sql code :

CREATE PROCEDURE [dbo].[usp_LIBRARYHideUnhideTreeNodes]
-- Add the parameters for the stored procedure here
@paramMyXml text,
@XmlHandle int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

• interfering with SELECT statements. SET NOCOUNT ON;

/*Update statements*/
EXEC sp_xml_preparedocument @XmlHandle output,@paramMyXml

UPDATE LIBRARYTree SET IsVisible = ~IsVisible WHERE NodeId in( SELECT xNodeID FROM OPENXML (@XmlHandle, 'HiddenNodes/Node',1) WITH ( xNodeID int '@id'))

EXEC sp_xml_removedocument @XmlHandle

END The corresponding code in pl/sql by sql developer is :

CREATE OR REPLACE PROCEDURE usp_LIBRARYHideUnhideTreeNodes

(
-- Add the parameters for the stored procedure here
v_paramMyXml IN CLOB DEFAULT NULL ,
v_XmlHandle OUT NUMBER
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

• interfering with SELECT statements. NULL/*TODO:SET NOCOUNT ON*/; /*Update statements*/ sp_xml_preparedocument(v_XmlHandle, v_paramMyXml); UPDATE LIBRARYTree SET IsVisible = UTL_RAW.BIT_COMPLEMENT(IsVisible) WHERE NodeId IN ( SELECT xNodeID FROM DUAL/*TODO:OPENXML (@XmlHandle, 'HiddenNodes/Node',1) WITH ( xNodeID int '@id'))*/ ); sp_xml_removedocument(v_XmlHandle); END;

This on compilation for obvious reasons gives errors.

how is sp_xml_preparedocument,openxml,sp_xml_removedocument handled in pl/sql??

any help in this direction will be appreciated..

Cheers,
Shishir. Received on Mon Jul 02 2007 - 14:57:24 CDT