The Column element is a child of the Table element. Each Column element has the following components:
- A name attribute that identifies the column name in the table
- An optional sqltype child element that describes the data type of the column
- An optional sqlexpr child element that specifies an SQL expression that generates the value for the column. Sqlexpr is required when the extracted value will be modified before shredding. Example:
<column name="Sal"><sqlexpr>case when Grade=10 then Sal_Temp*2 else Sal_Temp*3 END</sqlexpr></column>
See complete example in the next section.
- Additional attributes, such as an optional attribute named transient, which if true indicates that the associated value will be used in the computation of a target table column (that is, the data item itself will not be inserted into the target table).Transient is required when the extracted value will be used in sqlexpr. For transient columns, sqltype is mandatory since the column will not exist in the target table. Example:
<column name="Sal_Temp" transient="true" ref="_Sal_item_" path="Employee/Sal"><sqltype name="Integer"/></column>
See complete example in the next section.
The td:item attribute appears on element or attribute declarations in the schema. Validating XML parsers will ignore this attribute, just as they will ignore the annotations that represent the mappings.
The td:item attribute is used to provide a unique identifier for each element or attribute that can occur in an XML document. This unique identifier is used in the ref attribute of the Column elements to identify the element or attribute from which the value of the column is sourced when shredding the XML document to target tables. Every ref attribute on a Column element must match a td:item attribute defined elsewhere in the schema.
Example: Using sqlexpr and transient Within a Column Element
CREATE TABLE TRANSIENT_SQLEXPR_DOC(Id INTEGER, Xmldoc XML);
INSERT INTO TRANSIENT_SQLEXPR_DOC VALUES(1, CREATEXML('
<Employees>
<Employee>
<Id>1000</Id>
<Name>TIES</Name>
<Grade>10</Grade>
<Sal>40000</Sal>
</Employee>
<Employee>
<Id>1001</Id>
<Name>TIES</Name>
<Grade>11</Grade>
<Sal>60000</Sal>
</Employee>
</Employees>'));
CREATE TABLE EMP(Id Integer, Name VARCHAR(100), Grade Integer, Sal Decimal(18,4));
CALL TD_SYSXML.AS_SHRED_BATCH(
'sel id, xmldoc from TRANSIENT_SQLEXPR_DOC',
CREATEXML('<?xml version="1.0" encoding="ISO-8859-1"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:td="http://www.teradata.com/xml">
<xs:annotation>
<xs:appinfo>
<context xmlFeatureVersion="13.00.00.00" xmlns="http://www.teradata.com/xml">
<defaultDatabase>dr178882</defaultDatabase>
<defaultEncoding>ISO-8859-1</defaultEncoding>
<rootElement ref="_Employee_item_"/>
<transaction>
<operation type="insert">
<table name="EMP">
<column name="Id" ref="_Id_item_" path="Employee/Id"/>
<column name="Name" ref="_Name_item_" path="Employee/Name"/>
<column name="Grade" ref="_Grade_item_" path="Employee/Grade"/>
<column name="Sal_Temp" transient="true" ref="_Sal_item_" path="Employee/Sal"><sqltype name="Integer"/></column>
<column name="Sal"><sqlexpr>case when Grade=10 then Sal_Temp*2 else Sal_Temp*3 END</sqlexpr></column>
</table>
</operation>
</transaction>
</context>
</xs:appinfo>
</xs:annotation>
<xs:element name="Employees">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="Employee"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Employee" td:item="_Employee_item_">
<xs:complexType>
<xs:sequence>
<xs:element name="Id" td:item="_Id_item_" type="xs:int"/>
<xs:element name="Name" td:item="_Name_item_" type="xs:string"/>
<xs:element name="Grade" td:item="_Grade_item_" type="xs:int"/>
<xs:element name="Sal" td:item="_Sal_item_" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'), '', :res);
SEL * FROM EMP;
Query results:
Id Name Grade Sal
---------- ---------------------- ----------- --------------------
1000 TIES 10 80000.0000
1001 TIES 11 180000.0000