Oracle XML DB – Inserting XML
Friday, March 20th, 2015
Background
So following on from my previous post about creating XMLType tables in Oracle (either CLOB or schema based), here’s a few quick notes on getting data into the tables.
Inserting Data into XMLType Table
-- Create XMLType Table CREATE TABLE XML_TABLE OF XMLType -- create an Oracle directory to hold the XML (readable by Oracle) CREATE OR REPLACE DIRECTORY XML as '/home/foo/bar/xml' -- insert the record (reading from filesystem) -- filename.xml would be in directory you created above INSERT INTO XML_TABLE(SELECT XMLTYPE(bfilename('XML', 'filename.xml'), nls_charset_id('UTF8')) FROM dual);
Of course you could easily write some PL/SQL to iterate all files in the directory.
You can also insert XML directly via SQL:
INSERT INTO XML_TABLE VALUES(XMLType('<mxrecords> <mxrecord> <sysurn>sysurn1</sysurn> <eventid>eventid1</eventid> </mxrecord> <mxrecord> <sysurn>sysurn2</sysurn> <eventid>eventid2</eventid> </mxrecord> </mxrecords>' ));
You can leave a response, or trackback from your own site.
Tags: oracle, xml, xmldb, xmltype
Posted in: Examples, How to's, Oracle