|Oracle® XML DB Developer's Guide
10g Release 1 (10.1)
Part Number B10790-01
This chapter describes how to load XML data into Oracle XML DB with a focus on SQL*Loader.
This chapter contains these topics:
See Also:Chapter 3, " Using Oracle XML DB"
In Oracle9i release 1 (9.0.1) and higher, the Export-Import utility and SQL*Loader support
XMLType as a column type. In Oracle Database 10g, SQL*Loader also supports loading
XMLType tables and the loading is independent of the underlying storage. In other words, you can load XMLType data whether it is stored in LOBs or object-relationally. The
XMLType data can be loaded by SQL*Loader using both the conventional and direct path methods.
Note:One limitation is that SQL*Loader does not support direct path loading if the data involves inheritance.
XML columns are columns declared to be of type
XMLType columns and tables like any other object relational columns and tables. All methods described in the following sections for loading LOB data from the primary datafile or from LOBFILE, also apply to loading XMLType columns and tables when the XMLType data is stored as a LOB.
See Also:Oracle Database Utilities
Note:You cannot specify a SQL string for LOB fields. This is true even if you specify
XMLType data can be present in a control file or in a LOB file. In this case, the LOB file name is present in the control file.
Because XMLType data can be quite large, SQL*Loader can load LOB data from either a primary datafile (in line with the rest of the data) or from LOBFILEs independent of how the data is stored. That is, the underlying storage can still be object-relational. This section addresses the following topics:
Loading XMLType Data from a Primary Datafile
Loading XMLType Data from an External LOBFILE (BFILE)
Loading XMLType Data from LOBFILEs
Loading XMLType Data from a Primary Datafile
To load internal LOBs, Binary Large Objects (BLOBs), Character Large Objects (CLOBs), and National Character Large Object (NCLOBs), or
XMLType columns and tables from a primary datafile, use the following standard SQL*Loader formats:
Predetermined size fields
Length-value pair fields
These formats are described in the following sections and in more detail in Oracle Database Utilities.
This is a very fast and conceptually simple format to load LOBs.
Note: Because the LOBs you are loading may not be of equal size, you can use whitespace to pad the LOB data to make the LOBs all of equal length within a particular data field.
This format handles LOBs of different sizes within the same column (datafile field) without problem. However, this added flexibility can affect performance, because SQL*Loader must scan through the data, looking for the delimiter string.
As with single-character delimiters, when you specify string delimiters, you should consider the character set of the datafile. When the character set of the datafile is different than that of the control file, you can specify the delimiters in hexadecimal (that is, hexadecimal string). If the delimiters are specified in hexadecimal notation, then the specification must consist of characters that are valid in the character set of the input datafile. In contrast, if hexadecimal specification is not used, then the delimiter specification is considered to be in the client (that is, the control file) character set. In this case, the delimiter is converted into the datafile character set before SQL*Loader searches for the delimiter in the datafile.
LOB data can be lengthy enough so that it makes sense to load it from a LOBFILE instead of from a primary datafile. In LOBFILEs, LOB data instances are still considered to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILEs). Therefore, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.
There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader reads LOBFILEs in 64 KB chunks.
In LOBFILEs the data can be in any of the following types of fields:
A single LOB field into which the entire contents of a file can be read
Predetermined size fields (fixed-length fields)
Delimited fields (that is,
TERMINATED BY or
PRESERVE BLANKS is not applicable to fields read from a LOBFILE.
Length-value pair fields (variable-length fields)--
VARCHAR2 loader datatypes--are used for loading from this type of field.
All of the previously mentioned field types can be used to load XML columns.
You can specify LOBFILEs either statically (you specify the actual name of the file) or dynamically (you use a FILLER field as the source of the filename). In either case, when the EOF of a LOBFILE is reached, the file is closed and additional attempts to read data from that file produce results equivalent to reading data from an empty field.
You should not specify the same LOBFILE as the source of two different fields. If you do so, then typically, the two fields will read the data independently.
XMLType data can be loaded directly from the control file itself. In this release, SQL*Loader treats XMLType data like any other scalar type. For example, consider a table containing a NUMBER column followed by an XMLType column stored object-relationally. The control file used for this table can contain the value of the NUMBER column followed by the value of the XMLType instance.
SQL*Loader also accommodates XMLType instances that are very large. In this case you also have the option to load the data from a LOB file.
You can use SQL*Loader to load large amounts of XML data into Oracle Database.
Example 27-1 illustrates how to load XMLType data into Oracle Database.
Example 27-1 Loading Very Large XML Documents Into Oracle Database Using SQL*Loader
This example uses the control file,
load_data.ctl to load
XMLType data into table
This code registers the XML schema,
person.xsd, in Oracle XML DB and then creates table
foo. You can also create the table within the XML schema registration process.
CREATE TYPE person_t AS OBJECT(name VARCHAR2(100), city VARCHAR2(100));/ BEGIN -- delete schema if it already exists (else error) DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/person.xsd', 4); END;/ BEGIN DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/person.xsd', '<schema xmlns="http://www.w3.org/2001/XMLSchema"' || ' xmlns:per="http://www.oracle.com/person.xsd"' || ' xmlns:xdb="http://xmlns.oracle.com/xdb"' || ' elementFormDefault="qualified"' || ' targetNamespace="http://www.oracle.com/person.xsd">' || ' <element name="person" type="per:persontype"' || ' xdb:SQLType="PERSON_T"/>' || ' <complexType name="persontype" xdb:SQLType="PERSON_T">' || ' <sequence>' || ' <element name="name" type="string" xdb:SQLName="NAME"' || ' xdb:SQLType="VARCHAR2"/>' || ' <element name="city" type="string" xdb:SQLName="CITY"' || ' xdb:SQLType="VARCHAR2"/>' || ' </sequence>' || ' </complexType>' || '</schema>', TRUE, FALSE, FALSE); END;/ CREATE TABLE foo OF XMLType XMLSCHEMA "http://www.oracle.com/person.xsd" ELEMENT "person";
Here is the control file for loading
XMLType data using the registered XML schema, person.xsd:
LOAD DATA INFILE * INTO TABLE foo TRUNCATE XMLType(xmldata) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( xmldata ) BEGINDATA <person xmlns="http://www.oracle.com/person.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/person.xsd http://www.oracle.com/person.xsd"> <name> xyz name 2</name> </person>
Here is the SQL*Loader command for loading the XML data into Oracle Database:
sqlldr [username]/[password] load_data.ctl (optional: direct=y)
In the previous control file, the data was present in the control file itself, and a record spanned only one line. In the following example, the data is present in a separate file (
person.dat) from the control file (
lod2.ctl). Not only does the data file contain more than one row, but each row also spans multiple lines.
LOAD DATA INFILE * INTO TABLE foo TRUNCATE xmltype(xmldata) FIELDS ( fill filler char(1), xmldata LOBFILE (CONSTANT person.dat) TERMINATED BY '[XML]' ) BEGINDATA 0 0 0
The three zeroes after
BEGINDATA indicate that three records are present inthe file
person.dat (and they are terminated by
[XML]). The contents of
person.dat are as follows:
<person xmlns="http://www.oracle.com/person.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/person.xsd http://www.oracle.com/person.xsd"> <name>xyz name 2</name> </person> [XML] <person xmlns="http://www.oracle.com/person.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/person.xsd http://www.oracle.com/person.xsd"> <name> xyz name 2</name> </person> [XML] <person xmlns="http://www.oracle.com/person.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/person.xsd http://www.oracle.com/person.xsd"> <name>xyz name 2</name> </person> [XML] sqlldr [username]/[password] lod2.ctl (optional: direct=y)