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);
Result: To view the updated data in the target table, run: SELECT * FROM DefaultValue;
The last timestamp under the timestampwzc column is the default value.
datec | timec | timewzc | timestampc | timestampwzc |
---|---|---|---|---|
2000/09/09 | 04:12:12.000000 | 12:12:12.000000+00:00 | 2000-09-09 05:12:12.000000 | 2000-09-09 12:12:12.000000+00:00 |
3000/09/09 | 04:12:12.000000 | 12:12:12.000000+00:00 | 3000-09-09 05:12:12.000000 | 2014-11-04 15:22:56.000000-08:00 |