Load XML File into Teradata via BTEQ

access_time 3 months ago visibility50 comment 0

BTEQ is a Teradata utility tool that can be used to run Teradata SQL statements incl. DDL, DML, etc. It can also be used to import data from text file into Teradata databases. Like TPT and FASTLOAD, it can run in both batch and interactive modes.

This article demonstrates how to load XML file into Teradata.

Sample XML file

Create a sample XML file named sample.xml with the following content:

<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>

Sample table

Use the following statement to create the target table:

CREATE TABLE TestDb.BTEQ_XML_LOAD
(ID INT, XML_DOC XML) 
PRIMARY INDEX(ID);

Create a CSV index file

Create a CSV file named xml-docs.csv with the following content:

sample.xml,1
warning Note - you may encounter error 'Error: Teradata Database returned an invalid LOB token.' if the LOB (incl. XML, JSON) file name is not the first attribute. 

Create BTEQ script file 

Create BTEQ script file named load-xml.bteq with the following content:

LOGON 192.168.119.128/DBC,DBC;
DATABASE TestDb;
.IMPORT VARTEXT ',' DEFERCOLS=1 FILE='xml-docs.csv';
USING (XML_DOC XML AS DEFERRED, ID VARCHAR(10))
INSERT INTO BTEQ_XML_LOAD (CAST(:ID AS INTEGER), :XML_DOC);
LOGOFF ;

The above script reads from xml-docs.csv file and then use the first attribute as deferred.

Run the script file

Run the following command to execute above BTEQ script:

bteq < load-xml.bteq

Output:

bteq < load-xml.bteq
BTEQ 16.10.00.02 Mon Sep 14 17:02:53 2020 PID: 12352

+---------+---------+---------+---------+---------+---------+---------+----
LOGON 192.168.119.128/DBC,

 *** Logon successfully completed.
 *** Teradata Database Release is 16.10.00.03
 *** Teradata Database Version is 16.10.00.03
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
DATABASE TestDb;

 *** New default database accepted.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
.IMPORT VARTEXT ',' DEFERCOLS=1 FILE='xml-docs.csv';
+---------+---------+---------+---------+---------+---------+---------+----
USING (XML_DOC XML AS DEFERRED, ID VARCHAR(10))
INSERT INTO BTEQ_XML_LOAD (CAST(:ID AS INTEGER), :XML_DOC);

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
LOGOFF ;
 *** You are now logged off from the DBC.
 *** BTEQ exiting due to EOF on stdin.
 *** Exiting BTEQ...
 *** RC (return code) = 0

References

Query XML data

Refer to page Extract XML Data via SQL Functions in Teradata to find out details about how to extract XML data using native functions.

More details about BTEQ IMPORT command

info Last modified by Administrator at 3 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer SQL

visibility 979
thumb_up 0
access_time 2 years ago

This code snippet shows how to calculate time differences.

local_offer teradata local_offer SQL

visibility 363
thumb_up 0
access_time 9 months ago

From Teradata 16, LEAD and LAG OLAP functions are supported. The LAG function returns data from a row preceding the current row at a specified offset in a window group; the LEAD function accesses data from a row following the current row at a specified offset in a window group. Table  ...

local_offer teradata local_offer teradata-tool

visibility 16405
thumb_up 0
access_time 7 years ago

In this article, I am going to introduce how to install Teradata Express in virtual machines in Windows. 1) Download VMware Player for Windows 32-bit and 64-bit from the following link (version 6.0): https://my.vmware.com/web/vmware/free#desktop_end_user_computing/vmware_player/6_0 This is ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS