以下の例では、ユーザー テーブルに注釈付きスキーマを格納し、それを使用して、スキーマに照らして有効な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) );