Loading XML data

The load utility can be used for the efficient movement of large volumes of XML data into tables.

When loading data into an XML table column, you can use the XML FROM option to specify the paths of the input XML data file or files. For example, to load data from an XML file /home/user/xmlpath/xmlfile1.xml you could use the following command:

   LOAD FROM data1.del OF DEL XML FROM /home/user/xmlpath INSERT INTO USER.T1

The delimited ASCII input file data1.del contains an XML data specifier (XDS) that describes the location of the XML data to load. For example, the following XDS describes an XML document at offset 123 bytes in file xmldata.ext that is 456 bytes in length:

<XDS FIL='xmldata.ext' OFF='123' LEN='456' />

Loading XML data using a declared cursor is supported. The following example declares a cursor and uses the cursor and the LOAD command to add data from the table CUSTOMERS into the table LEVEL1_CUSTOMERS:

DECLARE cursor_income_level1 CURSOR FOR 
   SELECT * FROM customers
   WHERE XMLEXISTS('$DOC/customer[income_level=1]');

LOAD FROM cursor_income_level1 OF CURSOR INSERT INTO level1_customers;

The ANYORDER file type modifier of the LOAD command is supported for loading XML data into an XML column.

During load, distribution statistics are not collected for columns of type XML.

Loading XML data in a partitioned database environment

For tables that are distributed among database partitions, you can load XML data from XML data files into the tables in parallel. When loading XML data from files into tables, the XML data files must be read-accessible to all the database partitions where loading is taking place

Validating inserted documents against schemas

The XMLVALIDATE option allows XML documents to be validated against XML schemas as they are loaded. In the following example, incoming XML documents are validated against the schema identified by the XDS in the delimited ASCII input file data2.del:

   LOAD FROM data2.del OF DEL XML FROM /home/user/xmlpath XMLVALIDATE 
        USING XDS INSERT INTO USER.T2

In this case, the XDS contains an SCH attribute with the fully qualified SQL identifier of the XML schema to use for validation, "S1.SCHEMA_A":

<XDS FIL='xmldata.ext' OFF='123' LEN='456' SCH='S1.SCHEMA_A' />

Specifying parse options

You can use the XMLPARSE option to specify whether whitespace in the loaded XML documents is preserved or stripped. In the following example, all loaded XML documents are validated against the schema with SQL identifier "S2.SCHEMA_A" and these documents are parsed with whitespace preserved:

   LOAD FROM data2.del OF DEL XML FROM /home/user/xmlpath XMLPARSE PRESERVE 
   WHITESPACE XMLVALIDATE USING SCHEMA S2.SCHEMA_A INSERT INTO USER.T1