Example: Annotated Schema - Analytics Database - Teradata Vantage

XML Data Type

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
tkc1628112506748.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dgs1472251600184
lifecycle
latest
Product Category
Teradata Vantageā„¢

In this example, you store the annotated schema in a user table and then use it to shred an XML document that is valid according to the schema. You can use this schema to shred the sample XML document, Cust001.xml. The file containing the schema is named annotatedcustomerschema.xsd. The default database is XMLTYPE_TEST.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:td="http://www.teradata.com/xml">
   <xs:annotation>
      <xs:appinfo>
         <context xmlFeatureVersion="1.0" xmlns="http://www.teradata.com/xml">
            <defaultDatabase>XMLTYPE_TEST</defaultDatabase>
            <defaultEncoding>ISO-8859-1</defaultEncoding>
            <rootElement ref="_customer_item_"/>
            <transaction>
               <operation type="insert">
                  <table name="CUSTDTL">
                     <column name="ID" ref="_customerID_item_" path="Customer/@ID">
                        <sqltype name="char">
                           <constraint name="length">9</constraint>
                        </sqltype>
                     </column>
                     <column name="NAME" ref="_name_item_" path="Customer/Name">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="ADDRESS" ref="_address_item_" path="Customer/Address">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="PHONE1" ref="_phone1_item_" path="Customer/Phone1">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="PHONE2" ref="_phone2_item_" path="Customer/Phone2">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="FAX" ref="_fax_item_" path="Customer/Fax">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="EMAIL" ref="_email_item_" path="Customer/Email">
                         <sqltype name="varchar"/>
                     </column>
                  </table>
               </operation>
               <operation type="insert">
                  <table name="ORDERDTL">
                     <column name="CUSTOMER_ID" ref="_customerID_item_" path="Customer/@ID">
                        <sqltype name="char">
                           <constraint name="length">9</constraint>
                        </sqltype>
                     </column>
                     <column name="ORDER_ID" ref="_orderNumber_item_" path="Customer/Order/@Number">
                        <sqltype name="char">
                           <constraint name="length">11</constraint>
                        </sqltype>
                     </column>
                     <column name="ORDER_DATE" ref="_orderDate_item_" path="Customer/Order/@Date">
                        <sqltype name="date"/>
                     </column>
                     <column name="CONTACT" ref="_contact_item_" path="Customer/Order/Contact">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="CONTACT_PHONE" ref="_phone_item_" path="Customer/Order/Phone">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="SHIP_TO_ADDRESS" ref="_shipTo_item_" path="Customer/Order/ShipTo">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="SUB_TOTAL" ref="_subTotal_item_" path="Customer/Order/SubTotal">
                        <sqltype name="decimal"/>
                     </column>
                     <column name="TAX" ref="_tax_item_" path="Customer/Order/Tax">
                        <sqltype name="decimal"/>
                     </column>
                     <column name="TOTAL" ref="_total_item_" path="Customer/Order/Total">
                        <sqltype name="decimal"/>
                     </column>
                  </table>
               </operation>
               <operation type="insert">
                  <table name="LINEITEMDTL">
                     <column name="ORDER_ID" ref="_orderNumber_item_" path="Customer/Order/@Number">
                        <sqltype name="char">
                           <constraint name="length">11</constraint>
                        </sqltype>
                     </column>
                     <column name="ITEM_ID" ref="_itemID_item_" path="Customer/Order/Item/@ID">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="QUANTITY" ref="_quantity_item_" path="Customer/Order/Item/Quantity">
                        <sqltype name="integer"/>
                     </column>
                     <column name="PART_NUMBER" ref="_partNumber_item_" path="Customer/Order/Item/PartNumber">
                        <sqltype name="char">
                           <constraint name="length">6</constraint>
                        </sqltype>
                     </column>
                     <column name="DESCRIPTION" ref="_description_item_" path="Customer/Order/Item/Description">
                        <sqltype name="varchar"/>
                     </column>
                     <column name="UNIT_PRICE" ref="_unitPrice_item_" path="Customer/Order/Item/UnitPrice">
                        <sqltype name="decimal"/>
                     </column>
                     <column name="PRICE" ref="_price_item_" path="Customer/Order/Item/Price">
                        <sqltype name="decimal"/>
                     </column>
                  </table>
               </operation>
            </transaction>
         </context>
      </xs:appinfo>
   </xs:annotation>
   <xs:element name="Customer" td:item="_customer_item_">
      <xs:complexType>
         <xs:sequence>
            <xs:element name="Name" type="xs:string" td:item="_name_item_"/>
            <xs:element name="Address" type="xs:string" td:item="_address_item_"/>
            <xs:element name="Phone1" type="xs:string" td:item="_phone1_item_"/>
            <xs:element name="Phone2" type="xs:string" td:item="_phone2_item_"/>
            <xs:element name="Fax" type="xs:string" td:item="_fax_item_"/>
            <xs:element name="Email" type="xs:string" td:item="_email_item_"/>
            <xs:element ref="Order" maxOccurs="unbounded"/>
         </xs:sequence>
         <xs:attribute name="ID" type="xs:string" td:item="_customerID_item_"/>
      </xs:complexType>
   </xs:element>
   <xs:element name="Order" td:item="_order_item_">
      <xs:complexType>
         <xs:sequence>
            <xs:element name="Contact" type="xs:string" td:item="_contact_item_"/>
            <xs:element name="Phone" type="xs:string" td:item="_phone_item_"/>
            <xs:element name="ShipTo" type="xs:string" td:item="_shipTo_item_"/>
            <xs:element name="SubTotal" type="xs:float" td:item="_subTotal_item_"/>
            <xs:element name="Tax" type="xs:float" td:item="_tax_item_"/>
            <xs:element name="Total" type="xs:float" td:item="_total_item_"/>
            <xs:element ref="Item" maxOccurs="unbounded"/>
         </xs:sequence>
         <xs:attribute name="Number" type="xs:string" td:item="_orderNumber_item_"/>
         <xs:attribute name="Date" type="xs:date" td:item="_orderDate_item_"/>
      </xs:complexType>
   </xs:element>
   <xs:element name="Item">
      <xs:complexType>
         <xs:sequence>
            <xs:element name="Quantity" type="xs:integer" td:item="_quantity_item_"/>
            <xs:element name="PartNumber" type="xs:string" td:item="_partNumber_item_"/>
            <xs:element name="Description" type="xs:string" td:item="_description_item_"/>
            <xs:element name="UnitPrice" type="xs:float" td:item="_unitPrice_item_"/>
            <xs:element name="Price" type="xs:float" td:item="_price_item_"/>
         </xs:sequence>
         <xs:attribute name="ID" type="xs:string" td:item="_itemID_item_"/>
      </xs:complexType>
   </xs:element>
</xs:schema>

The tables referenced in the schema are defined as:

CREATE SET TABLE CUSTDTL ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT (
   ID            CHAR(9),
   NAME          VARCHAR(256),
   ADDRESS       VARCHAR(256),
   PHONE1        VARCHAR(16),
   PHONE2        VARCHAR(16),
   FAX           VARCHAR(16),
   EMAIL         VARCHAR(64)
) PRIMARY INDEX CUSTID_NUPI (ID);
CREATE SET TABLE ORDERDTL ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT (
   CUSTOMER_ID      CHAR(9),
   ORDER_ID         CHAR(11),
   ORDER_DATE       DATE,
   CONTACT          VARCHAR(256),
   CONTACT_PHONE    VARCHAR(16),
   SHIP_TO_ADDRESS  VARCHAR(256),
   SUB_TOTAL        DECIMAL(10,2),
   TAX              DECIMAL(10,2),
   TOTAL            DECIMAL(10,2)
) PRIMARY INDEX ORDERID_NUPI (ORDER_ID);
CREATE MULTISET TABLE LINEITEMDTL ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT (
   ORDER_ID          CHAR(11),
   ITEM_ID           VARCHAR(6),
   QUANTITY          INTEGER,
   PART_NUMBER       CHAR(6),
   DESCRIPTION       VARCHAR(512),
   UNIT_PRICE        DECIMAL(10,2),
   PRICE             DECIMAL(10,2)
);