UDFCALLNAME Error while using SHRED with TD14.10

General
Teradata Employee

UDFCALLNAME Error while using SHRED with TD14.10

I'm working to extract specific value from a CLOB field which store an XML content.

To get basics clear, i'm trying with the example given in (http://developer.teradata.com/extensibility/articles/xml-document-loading) article. While executing following query given in the article, i'm getting UDFCALLNAME error 3707.

Query (from article) trying:

select x.ID, x.C1, x.C2, x.C3, x.C4
from stage, table(sysxml.XMLXPATH_SHRED0(
stage.a, stage.b,
'',
'/CustomerList/Customer' ,
'./@id {1}, ./CustomerName/text() {2}, ./Address1/text() {3}, ./City/text() {4}')) x
where stage.a = x.Id
 
Error thrown on SQL-Assistant:
SELECT Failed 3707: Syntax error, expected something like an 'UDFCALLNAME' keyword between ',' and the word 'XMLPATH_SHRED0'
 
Please help on resolving it so that i can move ahead
 
Thanks,
Rohit

Accepted Solutions
Teradata Employee

Re: UDFCALLNAME Error while using SHRED with TD14.10

Finally, able to get close to solution desired and major glitch of reading XML from a clob field is resolved.

 

SyntaxEditor Code Snippet

LOCKING ROW FOR ACCESS 
SEL CAST(NEW XML (RESPONSE_XML).XMLEXTRACT('/RESPONSE/RESPONSES/ElementErrors/ElementError/Message', NULL)AS VARCHAR(255) CHARACTER SET UNICODE) abcd  ,response_xml
FROM <DB>.<tableName>
WHERE    <SomeOtherConditions>  AND 
LENGTH(CAST(NEW XML (RESPONSE_XML).XMLEXTRACT('/RESPONSE/RESPONSES/ElementErrors/ElementError/Message',
  NULL)  AS VARCHAR(255) CHARACTER 
SET UNICODE) ) > 0

This returns me all the list of error messages captured in xml file stored in CLOB field. 

1 ACCEPTED SOLUTION
6 REPLIES
Junior Contributor

Re: UDFCALLNAME Error while using SHRED with TD14.10

The article you reference is based on the pre-TD14.10 implementation of XML.

It was an add-on before but in TD14.10 ir's built-in, there's a newer reference:

 

https://developer.teradata.com/extensibility/articles/a-quick-tour-of-the-xml-type

Teradata Employee

Re: UDFCALLNAME Error while using SHRED with TD14.10

Thanks for the reply. I had been through the article mentioned in your post, before.

My issue is the solution is already implemented where column-field is defined as CLOB data-type instead XML type.

 

The CLOB field contains XML contain and i need to access a specific node value from there and I do not think changing CLOB to XML type would be seconded as it's already live.

 

Is there any way to solve the issue without changing type from CLOB to XML?

 

Thanks,

Rohit 

Junior Contributor

Re: UDFCALLNAME Error while using SHRED with TD14.10

Did you try CASTing the CLOB to XML?
Teradata Employee

Re: UDFCALLNAME Error while using SHRED with TD14.10

I tried with the help what's available online (i'm quite new to TD). Would you please help me to provide some sample with casting of CLOB into XML and fetch particular values?

 

Testing table structure:

create table STAGE (id VARCHAR(10), xml CLOB);

 

Data in XML field:

<CustomerList>
<Customer id=”1001”>
<CustomerName>John Doe</CustomerName>
<Address1>101 Maple Street</Address1>
<City>San Diego</City>
<State>CA</State>
<ZipCode>11111</ZipCode>
</Customer>
<Customer id=”2002”>
<CustomerName>Jane Doe</CustomerName>
<Address1>101 Maple Street2</Address1>
<City>San Diego</City>
<State>CA</State>
<ZipCode>11112</ZipCode>
</Customer>
</CustomerList>

 

How can i fetched the <Address1> of customer id='2002'?

 

Thanks,

Rohit

 

P.S. : I tried with (https://developer.teradata.com/extensibility/articles/xml-query) and all select queries (e.x. SELECT id, xmldoc.XMLEXTRACT('/Customers/Customer[1]', NULL) FROM T_XMLDOCS;) getting failed with 7548 (no other message). I used the same data set attached with the link.

Teradata Employee

Re: UDFCALLNAME Error while using SHRED with TD14.10

i'm still stuggeling with it.

Teradata Employee

Re: UDFCALLNAME Error while using SHRED with TD14.10

Finally, able to get close to solution desired and major glitch of reading XML from a clob field is resolved.

 

SyntaxEditor Code Snippet

LOCKING ROW FOR ACCESS 
SEL CAST(NEW XML (RESPONSE_XML).XMLEXTRACT('/RESPONSE/RESPONSES/ElementErrors/ElementError/Message', NULL)AS VARCHAR(255) CHARACTER SET UNICODE) abcd  ,response_xml
FROM <DB>.<tableName>
WHERE    <SomeOtherConditions>  AND 
LENGTH(CAST(NEW XML (RESPONSE_XML).XMLEXTRACT('/RESPONSE/RESPONSES/ElementErrors/ElementError/Message',
  NULL)  AS VARCHAR(255) CHARACTER 
SET UNICODE) ) > 0

This returns me all the list of error messages captured in xml file stored in CLOB field.