Posts Tagged ‘xsd’
Oracle XML DB – XMLType table creation
Wednesday, March 18th, 2015
Background
So I’ve been doing some Oracle XML work again. I’d lost my notes from last time (nice touch) so here are my notes from this time:
XMLType Table Creation
CREATE TABLE xml_table OF XMLType
This will create “an xml table” into which you can insert XML. It will check that the XML is well formed before insertion. It will not validate that all documents inserted are the same or that elements contain valid data.
For that we need to create a table based upon an XML Schema/XSD
-- create an Oracle directory where the schema can be stored create or replace directory XSD as '/home/foo/bar/oracle/xsd' -- copy your Schema into the directory -- then BEGIN DBMS_XMLSCHEMA.registerSchema(SCHEMAURL => 'http://www.foobar.com/xsd/my_schema.xsd', SCHEMADOC => bfilename('XSD','my_schema.xsd')); END; -- Note: the SCHEMAURL can be basically anything. Oracle uses it as a unique identifier -- Create the table CREATE TABLE matter OF xml_table XMLSCHEMA "http://www.foobar.com/xsd/my_schema.xsd" ELEMENT "nameOfTheRootXmlElement";
There are many overloaded versions of DBMS_XMLSCHEMA.registerSchema so you can shortcut some of the steps above. For example:
BEGIN DBMS_XMLSCHEMA.registerSchema( http://www.foobar.com/xsd/my_schema.xsd', bfilename('XSD','my_schema.xsd'), TRUE, -- generate required Oracle types. Default = true TRUE, -- generate javabeans. Default = false FALSE, -- generate errors when registering schema. True = do not TRUE -- generate table ); END;
Full docs for the DBMS_XMLSCHEMA package are here
here
Tags: oracle, schema, xml, xmldb, xmltype, xsd
Posted in Development, Examples, How to's | No Comments »