Example: Using FNC_GetXMLClob to Retrieve an XML Type Input Value - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

The following function takes an XML type parameter and retrieves the CLOB representation of the XML. It returns the CLOB back to the user.

CREATE FUNCTION xmlUDF2(P1 XML)
RETURNS CLOB AS LOCATOR
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!xmlUDF2!xmlUDF2.c';

void xmlUDF2(XML_HANDLE  *xml_handle,
              LOB_RESULT_LOCATOR *return_clob,
              int*       indicator_thisXML,
              int*       indicator_returnLOB,
              char       sqlstate[6],
              SQL_TEXT   extname[129],
              SQL_TEXT   specific_name[129],
              SQL_TEXT   error_message[257] )
{
     LOB_LOCATOR xmlClob;
     BYTE buffer[100000];
     LOB_CONTEXT_ID id;
     FNC_LobLength_t actlen;
     int trunc_err = 0;
     int numLobs;
     FNC_XMLSize_t xmlSize;
    
     FNC_GetXMLInfo(*xml_handle, &xmlSize, &numLobs);
  
      if(numLobs == 1) 
     {

      /* Read XML value */ 
       FNC_GetXMLClob(*xml_handle,&xmlClob); 

     /* Use LOB FNC calls to read the XML CLOB and return it */                                                                                                                           
      FNC_LobOpen(*xmlClob, &id, 0, 0);
      while( FNC_LobRead(id, buffer, 100000, &actlen) == 0 && !trunc_err)
          trunc_err = FNC_LobAppend(*return_clob, buffer, actlen, &actlen);

       FNC_LobClose(id);
      *indicator_returnLob = 0;
     }
 }