Querying XML Data stored in the database

Extensibility
Enthusiast

Querying XML Data stored in the database

Hello,
Is it possible to query XML data stored within Teradata, where the actual XML text is stored as a "CLOB" ?

I understand that Oracle supports XQuery. What's the answer for Teradata ?
Thanks
Tags (1)
5 REPLIES
Teradata Employee

Re: Querying XML Data stored in the database

Hi,

You need to search for TeraXML or TeradataXML ... forgot the exact name my apologies for that!

Regards, MAC
Enthusiast

Re: Querying XML Data stored in the database

Thanks MAC,
My understanding is that the Teradata XML product to which you refer allows you to save an XML document into the database as normal tables and columns, and then allow you to extract it back out into an xml document by a mapping xml schema definition.
I'm talking about storing an actual chunk of xml code in a varchar field and then searching/querying that column to extract the content. Xquery is one way to do this against an xml document, but not necessarily when it is in a database. (See www.w3.org/XML/Query).
What's the best way to go about this with the data stored in Teradata. I'm guessing I need to write my own app to do it ? Is there a plan to support this directly through SQL, dare I say it like Oracle ?
Senior Apprentice

Re: Querying XML Data stored in the database

XML Services doesn't support XQuery, but XPath functions can be used against XML CLOBs.

This is an example accessing DBQL data stored as XML in dbc.dbqlxmltbl (TD13):

select
queryid as QueryId,
t.result_value as StepText from
(select cast(queryid as varchar(18)), xmltextinfo
from dbc.dbqlxmltbl) as x(queryid, xmltextinfo),
table(sysxml.xmlextractvalues_u(x.queryid,
x.xmltextinfo, null, '//QuerySteps[@StepKind=''PJ'']/@StepText')) as t
where x.queryid = t.id1;

Dieter
Enthusiast

Re: Querying XML Data stored in the database

Thanks Dieter.
Enthusiast

Re: Querying XML Data stored in the database

You need to have a look at Teradata XML services orange book. It is understood to be using XALAN. Also you need to have Teradata XML services installed on your box to access these UDFs.