Skip to Main Content
  • Questions
  • Read XML and store it in local temporary table and select data from temp table for operation

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, nikita.

Asked: May 23, 2017 - 12:15 pm UTC

Last updated: May 24, 2017 - 1:38 am UTC

Version: 11

Viewed 1000+ times

You Asked

I need to read xml in stored procedure and need to store data to table after validating data. Please suggest how will I do it

and Connor said...

You can easily store it as xml within an xmltype column

SQL> create table t ( x xmltype);

Table created.

SQL>
SQL> declare
  2    my_xml clob :=
  3   '<entry xmlns="http://www.w3.org/2005/Atom">
  4   <id>https://toitest1.servicebus.net/oracle1</id>
  5   <title type="text">oracle1</title>
  6   <published>2016-11-30T19:32:43Z</published>
  7   <updated>2017-05-02T14:43:51Z</updated>
  8   <author><name>toitest1</name></author>
  9   <link rel="self" href="https://toitest1.servicebus.net/oracle1"/>
 10   <content type="application/xml">
 11   <QueueDescription xmlns="http://schemas.servicebus.com/netservices/2010/10/servicebus/connect" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
 12       <LockDuration>PT30S</LockDuration>
 13       <MaxSizeInMegabytes>16384</MaxSizeInMegabytes>
 14       <RequiresDuplicateDetection>false</RequiresDuplicateDetection>
 15       <RequiresSession>false</RequiresSession>
 16       <DefaultMessageTimeToLive>P14D</DefaultMessageTimeToLive>
 17       <DeadLetteringOnMessageExpiration>false</DeadLetteringOnMessageExpiration>
 18       <DuplicateDetectionHistoryTimeWindow>PT10M</DuplicateDetectionHistoryTimeWindow>
 19       <MaxDeliveryCount>10</MaxDeliveryCount><EnableBatchedOperations>true</EnableBatchedOperations>
 20       <SizeInBytes>357</SizeInBytes>
 21       <MessageCount>1</MessageCount>
 22   </QueueDescription>
 23   </content>
 24   </entry>';
 25
 26  begin
 27    insert into t values ( xmltype(my_xml));
 28  end;
 29  /

PL/SQL procedure successfully completed.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here