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

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

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

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

void xmlUDF5(XML_HANDLE  *xml_handle,
              LOB_RESULT_LOCATOR *return_blob,
              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 xmlBlob;
     BYTE buffer[100000];
     LOB_CONTEXT_ID id;
     FNC_LobLength_t actlen;
     int trunc_err = 0;
     FNC_XMLSize_t xmlSize;
     int numLobs;

     /* Get the XML Size */
      FNC_GetXMLInfo(*xml_handle, &xmlSize, &numLobs); 

     if(numLobs ==1 )
     {
    
      /* Read XML value */ 
       FNC_GetXMLBlob(*xml_handle,&xmlClob); 

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

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