FOR XML and OPENXML using Oracle / using xml in ORACLE stored procedure

I need a help with respect to xml operations in ORACLE.

For example, 
Need to send multiple rows at once into stored procedure.
Need to  pass data (a single entity) to stored procedure as a xml from Data Layer ( Instead of individual params)

In case of sql server, 
There is an approach like, 
    From Front End,

  •   Serialize the entity collection or single entity as xml and send to the db.
  •   Pass xml to stored procedure from Data Layer

    sql server stored proc in turn, 

  •   receives the xml as a ntext,
  •   converts into xmldocument and
  •   using OPENXML we can update/insert/delete the collection in single strech.

How to achieve the same functionality with ORACLE?
To my knowledge there is no sp_xml_preparedocument,OPENXML,FOR XML in ORACLE.
But there would be some indirect approach.

Can anyone share the ready made syntax or workaround on
like reading from a xml string and doing DB operations like insert,update,delete on tables..

Any help GREATLY appreciated.
Thanks in advance.

Best Regards,
Praveen Vitta.

3/15/2007 7:51:47 AM 2269 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 33

It depends what you are doing.  If all you are trying to do is avoid multiple round trips to the server then you don't need to use XML, just use array binds.  See

If you really do need to use XML try looking at the Oracle XML examples


3/18/2007 3:40:49 PM

Similar Artilces:

Oracle store procedure problem using Powerbuilder and Oracle Net
Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...

inserting XML Document into Oracle database using a stored procedure
------_=_NextPart_001_01C63DB4.0768C57B Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I am trying to insert a XMLType Xmldom.DOMDocument into the Oracle database and I am stuck. Thanks in advance for any pointers. =20 =20 Here is my stored procedure. The first parameter in the checkin_design stored procedure is a DOM object type parameter. The rest of the parameters are regular strings. =20 =20 =20 PROCEDURE checkin_design( in_designinfo_doc IN Xmldom.DOMDocument,=20 in_desi...

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

how to implement login module using Oracle 10g database (logic & Oracle 10g Queries) and how can i use membership and roles with Oracle 10g & VS 2.0
 Hi all, Can any one tell me how to implement login module using Oracle 10g database (logic & Oracle 10g Queries) and how can I use membership and roles with Oracle 10g & vs 2.0. , for implement this is it compulsory to install oracle 10g server on the same machine on which I'm implementing this task  or otherwise i can use  other system as a server (like remote login or client server)  Hi,You should have the Oracle 10g Server or Client In your machine.In Case Client is in your Machine then Server Must be somewhere,that is enough to use.I hope then we ...

Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle
Hello, I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something likereturn (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id); instead of using the way below (look at the C# code) Stored Procedure create or replace PROCEDURE "GET_ACCOUNT" ( p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur ) IS BEGIN OPEN retCursor FOR SELECT ID, NAME FROM ACCOUNT WHERE ID = p_ID ; ...

how to call an oracle stored function/procedure using ODBC.NET
Hi all, I have been trying to call an oracle stored funtion and cannot make it. Can anyone give me a clue. Thanks, Ted Code........ <%@ Page Language="VB" %> <%@ Import Namespace="System" %> <%@ Import Namespace="System.Web" %> <%@ Import Namespace="System.Web.UI" %> <%@ Import Namespace="System.Web.UI.WebControls" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Configuration" %> <%@ Import Namespace="Microsoft.Data...

Using RPCFUNC when calling Oracle Procedures/Functions in Oracle PACKAGES
I have looked at the documentation that I have, and found that RPCFUNC is the recommended option to call remote stored procedures. Does this, however, also work with Oracle packages (functions and procedures within an Oracle package)? I have done the following: 1. Created a user object (uo_transaction) inherited from transaction and had the following on its Decalre Local External Functions: FUNCTION string get_ins_address_zid(string address_type_zid, string address_line1, string address_line2, string address_line3, string brgy_zid) RPCFUNC ALIAS FOR "MAPI_CENTRAL.TBPKG__A...

Problem with Stored procedure execution using from PB10.5 using oracle 8i cleint
we are using PB10.5 with oracle 8i client software. we have a problem in getting the value from output parameter of oracle stored procedure. I.e the out put parameter is a numeric and it is supposed to return 7 digit number but it is returning 6 digit only. It is working fine if we are using oracle 9i cleint. But fails when we are using oracle8i client. Please help Hi Sailendra; Are you on the latest maintenance release by chance (PB 10.5.2)? -- Regards ... Chris ISUG - NA RUG Director <sailendra> wrote in message news:4a980...

which is shou i use? Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET
Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NETI am confused?Which is better or which one should I use?BTW, I am going to use .net 2.0 with oracle 9. Can anyone tell me what shou I use for working with oracle database?Thanks very much in advance!Justin Oracle's data provider has more functionality than Microsoft's.If your doing transaction processing you can have SAVEPOINTs, which Microsoft's data provider does not provide.Also, Oracle has PL/SQL Associative Arrays which are very powerful.Oracle also allows you to bind parameters by name and pos...

HELP PLEASE! Problems using returning a cursor to DBI & DBD::Oracle using Stored Procedures
Tim, Oracle 8.1.7 DBI 1.28 DBD::Oracle 1.12 I hope this email does not irriatate you but I really need some help. I = have an Oracle DBA I need to work with who doesn't seem to how to write = PL/SQL for Perl DBI. He insists on writting stored procedures which loop = through the results set =20 fetching the data into output variables. The problem is I can't seem to = figure out how to get all the data from it. I found an example of an oracle FUNCTION returning a cursor which = allowed me to fetch the data from Perl. Unfortanely, the DBA is = complaining that the return...

How to use stored procedure in Oracle
Hi, How to use Oracle stored procedure as a data source in Datawindow? TIA Hi, I am trying to use Oracle 8 - SP to create a DW PB -6.5. connecting to proc_demo4 SP from PB gives me the error : wrong number or type of arguments in call to SP. Could you please tell me what I am doing wrong here. I am a new Oracle developer, so kinda finding hard to understand references. Thanks Madhavee Daptardar drop PACKAGE a_demo4; CREATE OR REPLACE PACKAGE a_demo4 IS TYPE my_result_set IS REF CURSOR RETURN ecl_location%ROWTYPE; PROCEDURE proc_demo4 (as_comp IN VARCHAR...

Using Oracle Stored Procedures
How do you tell pb to use an oracle stored procedure to populate a crosstab datawindow. I have no problems getting data from the stored procedure using code, but I'm confused on how to populate the crosstab datawindow with the output. Michael Hasslinger Hi, There's a faxback document on how to use a Oracle stored procedure as datasource for a datawindow ( ) HTH -- - Met vriendelijke groet, Eric Aling [TeamPS], Cypres Informatisering bv, The Netherlands Michael Hasslinger wrote in ...

Connect to Oracle using Oracle Names
--27197788-12003-985187237=:188 Content-Type: TEXT/PLAIN; CHARSET=US-ASCII Content-Language: en-GBR Hi, I'm trying to connect to an Oracle database using Oracle Names but cannot get it to work. If I add the database to tnsnames.ora it works fine. Is Oracle Names not supported and if not will it ever be supported? Ben Schol ( --27197788-12003-985187237=:188 Content-Type: TEXT/x-cdsi-msrtf; CHARSET=US-ASCII Content-ID: 0 Content-Language: en-GBR {\rtf1\ansi\deff0{\fonttbl {\f0\fmodern\fcharset0 Courier New;}} \uc1\pard\lang1033\ulnone\f0\fs20 Hi...

Web resources about - FOR XML and OPENXML using Oracle / using xml in ORACLE stored procedure -

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...
Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...

Vin Scully To Miss Dodgers Postseason After Medical Procedure
... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Hall Of Fame Broadcaster Vin Scully Has Medical Procedure, Will Miss All Of 2015 Postseason
While the Dodgers will be in the 2015 MLB postseason, their Hall of Fame broadcaster, won't. Here's details on Vin Scully.

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 11/23/2015 11:22:28 AM