Extract XML Data via SQL Functions in Teradata

access_time 3 months ago visibility136 comment 0

XML data type is commonly supported in Teradata. Together with native XML type, a number of XML functions are added to support extracting values from XML, shredding and publishing JSON, etc. 

infoThe following code snippets use string literal to demonstrate the usage of these functions; you can replace them with any XML column in your table.

CREATEXML function

CREATEXML is used to convert VARCHAR column to XML column. 

Example:

SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>') AS XMLCol;

Output:

XMLExtract function

XMLEXTRACT function can be used to extract value from XML type columns.

Example:

WITH T AS
(
SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>') AS XMLCol
)
SELECT T.XMLCol.XMLEXTRACT('//txn[@id=3]/text()','') FROM T;

Output:

12.

XMLSERIALIZE function

XMLSerialize function can be used to convert XML type to other data types.

Example:

WITH T AS
(
SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>') AS XMLCol
)
SELECT XMLSerialize(CONTENT T.XMLCol.XMLEXTRACT('//txn/@id/string()','') AS VARCHAR(100)) as amount FROM T;

Output:

1 2 3

XMLQUERY function

XMLQUERY function can be used to evaluate values via XPath expression.

Example for querying XML attribute node:

WITH T AS
(
SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>') AS XMLCol
)
SELECT XMLQUERY('/category/@name/string()' 
PASSING BY VALUE T.XMLCol
RETURNING CONTENT
EMPTY ON EMPTY
) FROM T;

Output:

A

Example for querying Element node:

WITH T AS
(
SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>') AS XMLCol
)
SELECT XMLQUERY('//txn[@id>=2]/string()' 
PASSING BY VALUE T.XMLCol
RETURNING CONTENT
EMPTY ON EMPTY
) FROM T;

Output:

11. 12.

Another example:

WITH T AS
(
SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>') AS XMLCol
)
SELECT XMLQUERY('//txn[@id>=2]' 
PASSING BY VALUE T.XMLCol
RETURNING CONTENT
EMPTY ON EMPTY
) FROM T;

Output:

<txn id="2">11.</txn><txn id="3">12.</txn>

For more details about XPath expressions, refer to this W3C official documentation

XMLTABLE function

XMLTABLE function converts an XML tree structure to a row set.

Here is an example of omitting COLUMNS clause thus the items are returned as a XML column type:

WITH T AS
(
SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3">12.</txn>
	</txns>
</category>') AS XMLCol
)
SELECT X.* FROM T,
XMLTable(
'//txn' 
PASSING T.XMLCol
) AS X("ItemXml");

Output:

	ItemXml
1	<txn id="1">10.</txn>
2	<txn id="2">11.</txn>
3	<txn id="3">12.</txn>
Example with COLUMNS clause:
WITH T AS
(
SELECT CREATEXML('<category name="A">
	<txns>
		<txn id="1" date="2020-01-01">10.</txn>
		<txn id="2">11.</txn>
		<txn id="3" date="2020-01-03">12.</txn>
	</txns>
</category>') AS XMLCol
)
SELECT X.* FROM T,
XMLTable(
	'//txn' 
	PASSING T.XMLCol
	COLUMNS
	    "SeqNo" FOR ORDINALITY,
	    "TxnID" INT PATH '@id',
	    "TxnDate" DATE PATH '@date' DEFAULT DATE'9999-12-31',
	    "Amount" DECIMAL(20,2) PATH './text()'
) AS X(SeqNo,TxnID,TxnDate,Amount);

Output:

Once COLUMNS statement is added, the result is a table. 

XMLAGG function

XMLAGG function can be used to convert records to XML data type.

Create a table using the following statements:

create set table TestDb.test_table
(
id int not null,
category varchar(10),
amount int
)
primary index (id);

insert into TestDb.test_table values(1,'A',10);
insert into TestDb.test_table values(2,'A',11);
insert into TestDb.test_table values(3,'A',12);
insert into TestDb.test_table values(4,'B',100);
insert into TestDb.test_table values(5,'B',101);
insert into TestDb.test_table values(6,'B',102);
insert into TestDb.test_table values(4,'C',1000);
insert into TestDb.test_table values(5,'C',1001);
insert into TestDb.test_table values(6,'C',1002);

And then use XMLAGG function to create XML column:

SELECT category, XMLAGG(XMLELEMENT (NAME "txn", XMLATTRIBUTES(id as "id"), amount)
      ORDER BY id) as Transactions 
FROM TestDb.test_table 
group by category;

Output:


The output column Transactions is not a valid XML Document as there is no root element.

We can use XMLAGG together with XMLSERIALIZE function to create a valid XML document for each category in the table:

SELECT category, XMLSERIALIZE(DOCUMENT XMLDOCUMENT
   (XMLELEMENT(NAME "category",
   	  XMLATTRIBUTES(t.category as "name"),
	  XMLELEMENT(NAME "txns",
      XMLAGG(XMLELEMENT (NAME "txn", XMLATTRIBUTES(t.id as "id"), t.amount)
      ORDER BY t.id)
	  )
   )) AS VARCHAR(200)) AS "txns" 
   FROM TestDb.test_table t
GROUP BY category;

Result:

Another example is to convert the whole table records to a single XML document:

SELECT XMLSERIALIZE(DOCUMENT XMLDOCUMENT
   (XMLELEMENT(NAME "txns",
	      XMLAGG(XMLELEMENT (NAME "txn", 
		  					 XMLATTRIBUTES(t1.id as "id", t1.category as "category"),
							 t1.amount)
	      		ORDER BY t1.id
				)
		  )
   ) AS VARCHAR(2000)) AS "txns" 
   FROM (SELECT 0 AS GRP, t.* FROM TestDb.test_table t) AS t1
GROUP BY t1.GRP;
Output:
<txns><txn id="1" category="A">10.</txn><txn id="2" category="A">11.</txn><txn id="3" category="A">12.</txn><txn id="4" category="B">100.</txn><txn id="4" category="C">1000.</txn><txn id="5" category="C">1001.</txn><txn id="5" category="B">101.</txn><txn id="6" category="C">1002.</txn><txn id="6" category="B">102.</txn></txns>
For the serialized type, it needs to be large enough to contain all the elements. 
For XML publishing, you can also the following two procedures:
  • XMLPUBLISH_STREAM
  • XMLPUBLISH

Find more details on Teradata official documentation.

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

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 local_offer teradata-functions

visibility 346
thumb_up 0
access_time 5 months ago

In article   Teradata SQL - Use OREPLACE to Replace or Remove Characters , it shows how to use OREPLACE function in Teradata to replace or remove strings. This page will show one more alternative way of doing that using regular expressions. select oreplace('Kontext is a website for data ...

local_offer teradata local_offer SQL

visibility 69
thumb_up 0
access_time 3 months ago

This article demonstrates how to create volatile table in a Teradata procedure, perform DML actions (INSERT, DELETE, UPDATE) against it and then return the result set dynamically from the temporary table in the procedure.

local_offer teradata local_offer teradata-tool

visibility 16395
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