Example: XSLT_SHRED_BATCH Default Value for an Absent or Empty Column Value - Advanced SQL Engine - Teradata Database

A column element with <xsl:value-of> can have attributes named default. If default is specified its value will be inserted for Absent/Empty elements. Otherwise Teradata NULL will returned.

The following shows the syntax for using the default value.

<Row>
      <C_Name default=”Teradata Employee” >
          <xsl:value-of select=" …../AbsentOREmptyElement" />
      </C_Name >
</Row>

Before running the example, ensure the test user is created with the required permissions and the required tables are created. See Setting Up the XSLT_SHRED_BATCH and XSLT_SHRED Examples for details.

Populate the staging table used to store the XML source document.

DELETE Input_Docs;
DELETE DefaultValue;

INSERT INTO Input_Docs(1,NEW XML('
<Root>
 <AllTypes>
  <DateTime>
	  <Datec>2000-09-09</Datec>
	  <Timec>12:12:12</Timec>
	  <Timewzc>12:12:12+00:00</Timewzc>
	  <TimeStampc>2000-09-09T12:12:12</TimeStampc>
	  <TimeStampwzc>2000-09-09T12:12:12+00:00</TimeStampwzc>
  </DateTime>
 </AllTypes>
 <AllTypes>
  <DateTime>
	  <Datec>3000-09-09</Datec>
	  <Timec>12:12:12</Timec>
	  <Timewzc>12:12:12+00:00</Timewzc>
	  <TimeStampc>3000-09-09T12:12:12</TimeStampc>
  </DateTime>
 </AllTypes>
</Root>'));

Call the XSLT_SHRED_BATCH stored procedure with the stylesheet mapping to shred the XML data stored in the staging input table. The following arguments are used for the call.

• The queryString is: SELECT id, xmldoc FROM xsltuser.Input_Docs
• The xsltMapping argument is supplied by invoking the CREATEXML function with a stylesheet as input. This stylesheet will be applied to the XML document.
• The externalContext is NULL for this example.
• The resultCode is returned in :res. A successful operation returns 0.

CALL TD_SYSXML.XSLT_SHRED_BATCH('SELECT id, xmldoc FROM xsltuser.Input_Docs',
 CREATEXML('<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:date="http://exslt.org/dates-and-times"  
extension-element-prefixes="date" version="1.0" >
<xsl:param name="XML_TimeStamp" select="date:date-time()"/>
<xsl:template match="/Root">
 <Transaction>
  <Insert>
   <Table>
    <xsltuser.DefaultValue>
     <xsl:for-each select="AllTypes">
      <Row>
       <datec><xsl:value-of select="DateTime/Datec"/></datec>
       <timec><xsl:value-of select="DateTime/Timec"/></timec>
       <timewzc><xsl:value-of select="DateTime/Timewzc"/></timewzc>
       <timestampc>
         <xsl:value-of select="DateTime/TimeStampc"/>
       </timestampc>
       <timestampwzc default="{$XML_TimeStamp}">
         <xsl:value-of select="DateTime/TimeStampwzc"/>
       </timestampwzc>
      </Row>
     </xsl:for-each>
    </xsltuser.DefaultValue>
   </Table>
  </Insert>
 </Transaction>
</xsl:template>
</xsl:stylesheet>'), '',:res);