Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to emulate SQLServer's ( INSERT INTO SELECT FROM .... OPENXML ) functionality in Oracle 9i ???
KurtisK wrote:
> I am working on a project to port our SQLServer backend to Oracle. I am
> hoping to find a way to emulate
> this SQLServer T-SQL functionality in PL/SQL where a single INSERT statement
> can be used to process multiple row inserts
> from an incoming XML document. The insert must also be able to handle a
> variable in the insert such as the @iNumber variable below that
> is not part of the XML document.
>
> I've included a sample of the SQLServer code below and the results. 2 rows
> are inserted since there are two <Item> nodes in this document.
> I've tried several Oracle methods using XMLTYPE and DBMS_XMLDOM, but I have
> been unsuccessful so far. Any help would be appreciated.
>
>
> -- CREATE TABLE T ( C1 INT, C2 VARCHAR(10), C3 VARCHAR(10) )
>
> delete from t
>
> declare
> @iDoc int,
> @vcXMLDoc varchar(4000),
> @iNumber int
>
> set @iNumber = 1
> set @vcXMLDoc =
> '<Log><Item><OldValue>jones</OldValue><NewValue>JONES</NewValue></Item><Item
>
>><OldValue>smith</OldValue><NewValue>SMITH</NewValue></Item></Log>'
why not XSU:
declare
xml varchar2(2000);
ctx DBMS_XMLSave.ctxType;
cnt Number;
begin
xml:='<Log><Item><OldValue>jones</OldValue><NewValue>JONES</NewValue></Item>'
||'<Item><OldValue>smith</OldValue><NewValue>SMITH</NewValue></Item></Log>';
ctx:= DBMS_XMLSave.newContext('t');
DBMS_XMLSave.setRowTag(ctx,'item');
DBMS_XMLSave.setIgnoreCase(ctx,1);
cnt:= DBMS_XMLSave.insertXML(ctx,xml);
DBMS_XMLSave.closeContext(ctx);
end;
You can add global variable through package and trigger ;)
-- maniekReceived on Thu Mar 13 2003 - 15:54:38 CST