xml conversion in pl/sql

From: Shishir <shishir456_at_gmail.com>
Date: Mon, 02 Jul 2007 12:56:22 -0700
Message-ID: <1183406182.488679.278710_at_e16g2000pri.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
_at_paramMyXml text,
_at_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 _at_XmlHandle output,_at_paramMyXml

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

EXEC sp_xml_removedocument _at_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 (_at_XmlHandle, 'HiddenNodes/Node',1) WITH ( xNodeID int '_at_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 - 21:56:22 CEST

Original text of this message