16.20 - Example: FNC_GetXMLInfo, FNC_GetXML, and FNC_SetXML - 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)

This function takes an XML parameter and retrieves the XML value. It returns the string back in the XML return parameter.

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

void xmlUDF1(XML_HANDLE* xml_handle,
             XML_HANDLE* return_handle,
             int*        indicator_thisXML,
             int*        indicator_returnXML,
             char        sqlstate[6],
             SQL_TEXT    extname[129],
             SQL_TEXT    specific_name[129],
             SQL_TEXT    error_message[257] )
{
    FNC_XMLSize_t xmlSize;
    char* xmlBuffer;

    int xmlBufferSize;
    int numLobs;

    /* Get the XML Size. Note we use size + 2 to account for
       the Unicode null termination character. */
     FNC_GetXMLInfo(*xml_handle, &xmlSize, &numLobs); 
  
     if(numLobs == 0) 
    {
      xmlBuffer = (char*)FNC_Malloc(xmlSize + 2);
      xmlBufferSize = xmlSize + 1;

     /* Get the XML Value */
      FNC_GetXML(*xml_handle, xmlBuffer, xmlBufferSize, &xmlSize); 
                                                                                                   
     /* Set the XML string as return value */
      FNC_SetXML(*return_handle, xmlBuffer, xmlSize); 

     *indicator_returnXML = 0;
     FNC_free(xmlBuffer);
    }
}