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
C1 INTEGER
C2 XML
C3 VARCHAR(10)
C4 XML
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
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
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
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
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