22
Loading XML Data into Oracle XML DB

This chapter describes how XML data can be loaded into Oracle XML DB using SQL*Loader.

It contains the following sections:

• Loading XMLType Data into Oracle9i Database
• Using SQL*Loader to Load XMLType Columns

Loading XMLType Data into Oracle9i Database

In Oracle9i Release 1 (9.0.1) and higher, both Export/Import utilities and SQL*Loader support XMLType as a column type. In other words, if your table has a column of type XMLType, it can be properly Exported and Imported from and to Oracle9i database, and you can load XML data into that column using SQL*Loader.

Restoration

In the current release, Oracle XML DB Repository information is not exported when user data is exported. This means that the resources and all information is not exported.

Using SQL*Loader to Load XMLType Columns

XML columns are columns declared to be of type XMLType.

SQL*Loader treats XML columns as if they are CLOBs. All methods described in the following sections for loading LOB data from the primary datafile or from LOBFILEs are applicable to loading XML columns.

Because LOBs can be quite large, SQL*Loader is able to load LOB data from either a primary datafile (in line with the rest of the data) or from LOBFILEs. This section addresses the following topics:

• Loading LOB Data from a Primary Datafile
• Loading LOB Data from an External LOBFILE (BFILE)
• Loading LOB Data from LOBFILEs
• Loading LOB Data from a Primary Datafile

To load internal LOBs (BLOBs, CLOBs, and NCLOBs) or XML columns from a primary datafile, you can use the following standard SQL*Loader formats:

• Predetermined size fields
• Delimited fields
• Length-value pair fields

These formats is described in the following sections and in Oracle9i Database Utilities.

LOB Data in Predetermined Size Fields

This is a very fast and conceptually simple format in which 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.

LOB Data in Delimited Fields

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, 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, the delimiter specification is considered to be in the client's (that is, the control file's) character set. In this case, the delimiter is converted into the datafile's character set before SQL*Loader searches for the delimiter in the datafile.

Loading LOB Data from LOBFILEs

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 ENCLOSED BY)
  • The clause PRESERVE BLANKS is not applicable to fields read from a LOBFILE.
  • Length-value pair fields (variable-length fields)--VARRAW, VARCHAR, or VARCHARC 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.

Dynamic Versus Static LOBFILE Specifications

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 further 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, typically, the two fields will read the data independently.