DB2 10.5 for Linux, UNIX, and Windows

Exporting XML data

When exporting XML data, the resulting QDM (XQuery Data Model) instances are written to a file or files separate from the main data file containing exported relational data. This is true even if neither the XMLFILE nor the XML TO option is specified.

By default, exported QDM instances are all concatenated to the same XML file. You can use the XMLINSEPFILES file type modifier to specify that each QDM instance be written to a separate file.

The XML data, however, is represented in the main data file with an XML data specifier (XDS). The XDS is a string represented as an XML tag named "XDS", which has attributes that describe information about the actual XML data in the column; such information includes the name of the file that contains the actual XML data, and the offset and length of the XML data within that file.

The destination paths and base names of the exported XML files can be specified with the XML TO and XMLFILE options. If the XML TO or XMLFILE option is specified, the format of the exported XML file names, stored in the FIL attribute of the XDS, is xmlfilespec.xxx.xml, where xmlfilespec is the value specified for the XMLFILE option, and xxx is a sequence number for xml files produced by the export utility. Otherwise, the format of the exported XML file names is: exportfilename.xxx.xml, where exportfilename is the name of the exported output file specified for the EXPORT command, and xxx is a sequence number for xml files produced by the export utility.

By default, exported XML files are written to the path of the exported data file. The default base name for exported XML files is the name of the exported data file, with an appending 3-digit sequence number, and the .xml extension.

Examples

For the following examples, imagine a table USER.T1 containing four columns and two rows:

   C1 INTEGER
   C2 XML
   C3 VARCHAR(10)
   C4 XML

Table 1. USER.T1
C1	C2	C3	C4
2	<?xml version="1.0" encoding="UTF-8" ?><note time="12:00:00"><to>You</to><from> Me</from><heading>note1</heading> <body>Hello World!</body></note>	'char1'	<?xml version="1.0" encoding="UTF-8" ?><note time="13:00:00"><to>Him</to><from> Her</from><heading>note2</heading>< body>Hello World!</body></note>
4	NULL	'char2'	?xml version="1.0" encoding="UTF-8" ?><note time="14:00:00">to>Us</to><from> Them</from><heading>note3</heading> <body>Hello World!</body></note>

Example 1

The following command exports the contents of USER.T1 in Delimited ASCII (DEL) format to the file "/mypath/t1export.del". Because the XML TO and XMLFILE options are not specified, the XML documents contained in columns C2 and C4 are written to the same path as the main exported file "/mypath". The base name for these files is "t1export.del.xml". The XMLSAVESCHEMA option indicates that XML schema information is saved during the export procedure.

   EXPORT TO /mypath/t1export.del OF DEL XMLSAVESCHEMA SELECT * FROM USER.T1

The exported file "/mypath/t1export.del" contains:

   2,"<XDS FIL='t1export.del.001.xml' OFF='0' LEN='144' />","char1",
   "<XDS FIL='t1export.del.001.xml' OFF='144' LEN='145' />"
   4,,"char2","<XDS FIL='t1export.del.001.xml' OFF='289' 
   LEN='145' SCH='S1.SCHEMA_A' />"

The exported XML file "/mypath/t1export.del.001.xml" contains:

   <?xml version="1.0" encoding="UTF-8" ?><note time="12:00:00"><to>You</to>
      <from>Me</from><heading>note1</heading><body>Hello World!</body>
      </note><?xml version="1.0" encoding="UTF-8" ?><note time="13:00:00"><to>Him
      </to><from>Her</from><heading>note2</heading><body>Hello World!
      </body></note><?xml version="1.0" encoding="UTF-8" ?><note time="14:00:00">
      <to>Us</to><from>Them</from>heading>note3</heading><body>
      Hello World!</body></note>

Example 2

The following command exports the contents of USER.T1 in DEL format to the file "t1export.del". XML documents contained in columns C2 and C4 are written to the path "/home/user/xmlpath". The XML files are named with the base name "xmldocs", with multiple exported XML documents written to the same XML file. The XMLSAVESCHEMA option indicates that XML schema information is saved during the export procedure.

   EXPORT TO /mypath/t1export.del OF DEL XML TO /home/user/xmlpath 
   XMLFILE xmldocs XMLSAVESCHEMA SELECT * FROM USER.T1

The exported DEL file "/home/user/t1export.del" contains:

   2,"<XDS FIL='xmldocs.001.xml' OFF='0' LEN='144' />","char1",
   "<XDS FIL='xmldocs.001.xml' OFF='144' LEN='145' />"
   4,,"char2","<XDS FIL='xmldocs.001.xml' OFF='289' 
   LEN='145' SCH='S1.SCHEMA_A' />"

The exported XML file "/home/user/xmlpath/xmldocs.001.xml" contains:

   <?xml version="1.0" encoding="UTF-8" ?><note time="12:00:00"><to>You</to>
      <from>Me</from><heading>note1</heading><body>Hello World!</body>
      </note><?xml version="1.0" encoding="UTF-8" ?><note time="13:00:00">
      <to>Him</to><from>Her</from><heading>note2</heading><body>
      Hello World!</body></note><?xml version="1.0" encoding="UTF-8" ?>
      <note time="14:00:00"><to>Us</to><from>Them</from><heading>
      note3</heading><body>Hello World!</body></note>

Example 3

The following command is similar to Example 2, except that each exported XML document is written to a separate XML file.

   EXPORT TO /mypath/t1export.del OF DEL XML TO /home/user/xmlpath 
   XMLFILE xmldocs MODIFIED BY XMLINSEPFILES XMLSAVESCHEMA 
   SELECT * FROM USER.T1

The exported file "/mypath/t1export.del" contains:

   2,"<XDS FIL='xmldocs.001.xml' />","char1","XDS FIL='xmldocs.002.xml' />"
   4,,"char2","<XDS FIL='xmldocs.004.xml' SCH='S1.SCHEMA_A' />"

The exported XML file "/home/user/xmlpath/xmldocs.001.xml" contains:

   <?xml version="1.0" encoding="UTF-8" ?><note time="12:00:00"><to>You</to>
      <from>Me</from><heading>note1</heading><body>Hello World!</body>
      </note>

The exported XML file "/home/user/xmlpath/xmldocs.002.xml" contains:

   ?xml version="1.0" encoding="UTF-8" ?>note time="13:00:00">to>Him/to>
      from>Her/from>heading>note2/heading>body>Hello World!/body>
      /note>

The exported XML file "/home/user/xmlpath/xmldocs.004.xml" contains:

   <?xml version="1.0" encoding="UTF-8" ?><note time="14:00:00"><to>Us</to>
      <from>Them</from><heading>note3</heading><body>Hello World!</body>
      </note>

Example 4

The following command writes the result of an XQuery to an XML file.

   EXPORT TO /mypath/t1export.del OF DEL XML TO /home/user/xmlpath 
   XMLFILE xmldocs MODIFIED BY XMLNODECLARATION select 
   xmlquery( '$m/note/from/text()' passing by ref c4 as "m" returning sequence) 
      from USER.T1

The exported DEL file "/mypath/t1export.del" contains:

   "<XDS FIL='xmldocs.001.xml' OFF='0' LEN='3' />"
   "<XDS FIL='xmldocs.001.xml' OFF='3' LEN='4' />"

The exported XML file "/home/user/xmlpath/xmldocs.001.xml" contains:

   HerThem

Note: The result of this particular XQuery does not produce well-formed XML documents. Therefore, the file exported in this example, could not be directly imported into an XML column.