All Forums Extensibility
Willimoa 63 posts Joined 10/09
01 Sep 2010
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

Adeel Chaudhry 773 posts Joined 04/08
03 Sep 2010

Hi,

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

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

Willimoa 63 posts Joined 10/09
06 Sep 2010

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 ?

dnoeth 4628 posts Joined 11/04
07 Sep 2010

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

Dieter

Willimoa 63 posts Joined 10/09
16 Nov 2010

Thanks Dieter.

enjoycoding 20 posts Joined 08/10
28 Dec 2010

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.

You must sign in to leave a comment.