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 -> Re: How to emulate SQLServer's ( INSERT INTO SELECT FROM .... OPENXML ) functionality in Oracle 9i ???

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

From: Uma Muthu <Mail_at_NoSpamPlease.com>
Date: Wed, 12 Mar 2003 22:23:21 GMT
Message-ID: <trOba.1463$cG1.590803@news1.news.adelphia.net>

Good Question KurtisK... But I am also looking for the answer...

"KurtisK" <KJKYLE_at_COOLBLUENOSPAM.COM> wrote in message news:IPMba.1415$cG1.564915_at_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.
>
>
> -- 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>'
>
> 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 - 16:23:21 CST

Original text of this message

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