例: 注釈付きスキーマ - Teradata Database - Teradata Vantage NewSQL Engine

以下の例では、ユーザー テーブルに注釈付きスキーマを格納し、それを使用して、スキーマに照らして有効なXML文書のシュレッドを実行します。このスキーマを使用してサンプルXML文書Cust001.xmlをシュレッドすることができます。このスキーマを含むファイルの名前はannotatedcustomerschema.xsdです。デフォルトのデータベースは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>

スキーマで参照されているテーブルは次のように定義されています。

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)
);