Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> How to emulate SQLServer's ( INSERT INTO SELECT FROM .... OPENXML ) functionality in Oracle 9i ???

How to emulate SQLServer's ( INSERT INTO SELECT FROM .... OPENXML ) functionality in Oracle 9i ???

From: KurtisK <KJKYLE_at_COOLBLUENOSPAM.COM>
Date: Wed, 12 Mar 2003 20:32:40 GMT
Message-ID: <IPMba.1415$cG1.564915@news1.news.adelphia.net>


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.

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>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @vcXMLDoc

INSERT INTO T
  SELECT
   @iNumber,
   OldValue,
   NewValue

  FROM OPENXML(@iDoc, '/Log/Item/NewValue')

      WITH  (OldValue   varchar(255) '../OldValue',
        NewValue    varchar(255) '.' )


EXEC sp_xml_removedocument @iDoc

select * from t

C1 C2 C3
----------- ---------- ----------

1           jones      JONES
1           smith      SMITH
Received on Wed Mar 12 2003 - 14:32:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US