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