These are the typical tasks you perform to store the text representation of XML documents in an XML column in a table:
- Create a table defined with XML columns that will contain your XML documents.
- Determine an appropriate method for loading the documents into the table.Note that some load utilities, such as FastLoad and MultiLoad, do not support LOBs or XML. However, you can use these utilities to load XML data in the following cases:
- You can load XML data if it is less than 64 KB, and the target table defines the column as CHAR or VARCHAR.
- If you use a transform group that converts XML to/from VARCHAR or VARBYTE.
When loading XML data using FastLoad or MultiLoad using VARCHAR or VARBYTE transforms, the imported data must fit in the row. If it cannot be stored inline, the input row is put into the error table.
For documents larger than 64KB, use a utility that has LOB support:- The MLOADX protocol can load LOB XML data using any transforms without the restriction of the inline length specified for the type.
- Teradata Parallel Transporter fully supports loading large XML documents.
- BTEQ only supports loading large XML documents in ASCII session character set which is not the default character set for XML.
- Insert the XML text into the XML columns using the NEW XML operator or the CREATEXML function.
When loading your XML documents:
- Consider the encoding of the XML document if the values will be translated to the session character set. For example, if you load the document as VARCHAR, you can get errors if the document contains characters that cannot be represented in the session character set.
- Consider shredding parts of the XML document to columns of other SQL types that can be indexed which allows for better performance when retrieving the XML values.