Example: Annotated Schema - Analytics Database - 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)
);