Need Query for reading XML data from a CLOB object

Database
Enthusiast

Need Query for reading XML data from a CLOB object

Hi Folks,

I am an user of Teradata (DataBase version 14.0). I have a requirement to extract / read the XML data from the DB which is stored as a CLOB object.

1) Since we don't find any datatypes like "XML" on TD 14.0 , we proceeded with storing the XML data into CLOB object and trying to

retrieve it from there. Can someone pls opine if this is the right approach that we have taken ?

2) Also, the very reason to start this topic is to request for the SQL query in teradata to parse and fetch the XML data stored in CLOB column. We have seen examples using the functions like XMLEXTRACT but that are not seem to be working out on our case. We get an Error when we try to execute any of the queries provided below.

select x.ID,x.C1,x.C2,x.C3,x.C4 from ESA_XML_DATAVALIDATION, table(sysxml.XMLXPATH_SHRED0

(ESA_XML_DATAVALIDATION.a, ESA_XML_DATAVALIDATION.b,'','/CustomerList/Customer' ,'./@id {1}, ./CustomerName/text() {2},

./Address1/text() {3}, ./City/text() {4}')) x where ESA_XML_DATAVALIDATION.a = x.Id;

select

queryid as QueryId,

t.result_value as StepText from

(select cast(id as varchar(18)), xml_file

from DV_SHA_POC_DB.ESA_XML_DATAVALIDATION) as x(id, xml_file),

table(sysxml.xmlextractvalues_u(x.queryid,

x.xmltextinfo, null, '//QuerySteps[@StepKind=''PJ'']/@StepText')) as t

where x.queryid = t.id1;

SELECT XPathValue(O.xml_file, '//ORDER/PO_NUMBER/*') AS PO_Number,XPathValue(O.xml_file, '//ORDER/DATE/*') AS theDate

FROM ESA_XML_DATAVALIDATION O

WHERE

XPathValue(O.xml_file,'//ORDER/BILLTO/*') = 'Mike' and id = 2;

The queries (1 & 3) are something we got by doing a "Google" on the WEB. None of these queries worked when we tried to execute with the sample XML's provided on the WEB.

Can some one pls point to the right direction here and come up with Sample XML queries that might work in this scenario ? Any help is greatly appreciated.

Thanks,

Murugesan

4 REPLIES
Junior Contributor

Re: Need Query for reading XML data from a CLOB object

Hi Murugesan,

you should have a look at Teradata XML Services for TD14, in 14.10 this will be built-in.

Enthusiast

Re: Need Query for reading XML data from a CLOB object

Hi Dieter,

Not sure of my TD server's OS type. Ill let you know on that.

Also, on the above examples that i have posted in here, there were few sysxml functions being

used like sysxml.XMLXPATH_SHRED0,sysxml.xmlextractvalues_u to extract values from XML.

But when i open up my sysxml DB functions, i don't see the above two functions. But, i do see

functions like below :

XMLEXTRACT_LOB , XMLEXTRACT_LOB_U , XMLEXTRACT_VC_LOB_U , XMLEXTRACTVALUE_LOB,

XMLEXTRACTVALUE_LOB_U, XMLEXTRACTVALUE_VC, XMLELEMENT1,XMLELEMENT1_U,XMLATTRIBUTES1, XMLATTRIBUTES1_U , XMLISVALID_LOB,XMLISVALID_LOB_U

When i do a "right click " and try to see the definitions of any of the functions on sysXML DB, I clearly get a message at the bottom of the TSA stating "The User does not have access to SYSXML.xxxx" where xxxx is the function name.

So, I guess the Teradata Admin has to provide the access to the list of necessary tables that we have to use. But, please let me know what should we do in case of missing functions that i listed above. Please revert for any clarifications.

Enthusiast

Re: Need Query for reading XML data from a CLOB object

Hi Dieter,

1) I face a strange problem on the length of the column values displayed on Teradata SQL assistant (TSA). I read XML data from the CLOB column (Query provided below) and if any of the column values has more than 50 CHARACTERS, it's getting truncated and only the first 50 characters are shown ALL THE TIME. I have tried for multiple tables and same problem persists for all the tables.

This is for the requirement where in ill have to load the XML into Teradata as CLOB and retrieve the XML data from there. I am using BTEQ script to load the XML data into the teradata database and it's working fine without any issues.Also, I was able to form the query for parsing the XML data stored as a CLOB. The function that i use is, XMLPATH_SHRED0. Below is the sample Query:

 select COALESCE(CAST(x.C1 AS VARCHAR(700)),'') as Code,

 COALESCE(CAST(x.C2 AS VARCHAR(700)),'') as Name,

 COALESCE(CAST(x.C3 AS VARCHAR(700)),'') as PapType,

 COALESCE(CAST(x.C4 AS VARCHAR(700)),'') as Comments,

 COALESCE(CAST(x.C5 AS VARCHAR(700)),'') as Verbiage,

 COALESCE(CAST(x.C6 AS VARCHAR(700)),'') as PromoType

from DV_SHA_DCL_SEAWARE_DB.STAGE , table(

 sysxml.XMLXPATH_SHRED0(DV_SHA_DCL_SEAWARE_DB.STAGE.id,DV_SHA_DCL_SEAWARE_DB.STAGE.xml_file,'',

'/DclCdrLookupData/Promos/Promo',

'./@Code  {1}

,./@Name  {2}

,./@PapType  {3}

,./@Comments  {4}

,./@Verbiage  {5}

,./@PromoType  {6}')

) x where DV_SHA_DCL_SEAWARE_DB.STAGE.id = 5 minus

select COALESCE(CAST(Code AS VARCHAR(700)),'') as Code,

 COALESCE(CAST(Name AS VARCHAR(700)),'') as Name,

 COALESCE(CAST(PapType AS VARCHAR(700)),'') as PapType,

 COALESCE(CAST(Comments AS VARCHAR(700)),'') as Comments,

 COALESCE(CAST(Verbiage AS VARCHAR(700)),'') as Verbiage,

 COALESCE(CAST(PromoType AS VARCHAR(700)),'') as PromoType

FROM DV_SHA_DCL_SEAWARE_DB.Promo where ETL_TARG_REC_SET_ID = (select max(ETL_TARG_REC_SET_ID) from DV_SHA_DCL_SEAWARE_DB.Promo);

The idea of the above query is that, we load the CLOB data into the table "STAGE" on the column "xml_file". The clob XML data extracted for a particular table on the src is compared against the target using a "MINUS" query.

Please help in letting me know what could possibly be the reason for the data truncation on the columns. Is this something that could be solved just by tweaking the settings on SQL assistant or the inability of the XML function that i use ?

2) Also, The Teradata XML services link that you have provided above (http://downloads.teradata.com/node/896) doesn't seem to contain the link for TD version 14.0 for windows. Also, the existing one for the version 13.10 (Windows) was also NOT downloadable.

Can you pls look into this and send me any latest link that you have for TD 14.0 FOR WINDOWS ? Sorry, if i have over-looked anything here.

Thanks,

Murugesan

Enthusiast

Re: Need Query for reading XML data from a CLOB object

Hi,

Also, since I doesn't know the exact syntax of the XMLPATH_SHRED0 , I don’t have an idea of how it can be used to full extent. Would it also be possible for you to send me the syntax structure of all the XML functions that you are aware of ? That would of great help.

Many Thanks,

Gobind B