Examples in this Document | XML Data Type | Teradata Vantage - Examples in this Document - Advanced SQL Engine - Teradata Database

The examples in this document reference the customer and customerText tables defined as follows:

CREATE TABLE customer (
   customerID INTEGER,
   customerName VARCHAR(256),
   customerXML XML )
   PRIMARY INDEX (customerID);

CREATE TABLE customerText (
   customerID INTEGER,
   customerName VARCHAR(256),
   customerXMLText CLOB )
   PRIMARY INDEX (customerID);

The examples use a sample XML document in a file named Cust001.xml. The contents of this file are as follows:

<?xml version="1.0" encoding="UTF-8"?>
<Customer>
   <Name>John Hancock</Name>
   <Address>100 1st Street, San Francisco, CA 94118</Address>
   <Phone1>(858)555-1234</Phone1>
   <Phone2>(858)555-9876</Phone2>
   <Fax>(858)555-9999</Fax>
   <Email>John@somecompany.com</Email>
   <Order Number="NW-01-16366" Date="2012-02-28">
      <Contact>Mary Jane</Contact>
      <Phone>(987)654-3210</Phone>
      <ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
      <SubTotal>434.99</SubTotal>
      <Tax>32.55</Tax>
      <Total>467.54</Total>
      <Item ID="001">
         <Quantity>10</Quantity>
         <PartNumber>F54709</PartNumber>
         <Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
         <UnitPrice>29.50</UnitPrice>
         <Price>295.00</Price>
      </Item>
      <Item ID="101">
         <Quantity>1</Quantity>
         <PartNumber>Z19743</PartNumber>
         <Description>Motorola Milestone XT800 Cell Phone</Description>
         <UnitPrice>139.99</UnitPrice>
         <Price>139.99</Price>
      </Item>
   </Order>
</Customer>

The sample XML document contains a Customers/Customer/Order/Item hierarchy. The examples use a schema file named customerschema.xsd that specifies the grammar for this XML document. The contents of customerschema.xsd are as follows:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="Item">
      <xs:complexType>
         <xs:sequence>
            <xs:element name="Quantity" type="xs:integer"/>
            <xs:element name="PartNumber" type="xs:string"/>
            <xs:element name="Description" type="xs:string"/>
            <xs:element name="UnitPrice" type="xs:float"/>
            <xs:element name="Price" type="xs:float"/>
         </xs:sequence>
         <xs:attribute name="ID" type="xs:string"/>
      </xs:complexType>
   </xs:element>

   <xs:element name="Order">
      <xs:complexType>
         <xs:sequence>
            <xs:element name="Contact" type="xs:string"/>
            <xs:element name="Phone" type="xs:string"/>
            <xs:element name="ShipTo" type="xs:string"/>
            <xs:element name="SubTotal" type="xs:float"/>
            <xs:element name="Tax" type="xs:float"/>
            <xs:element name="Total" type="xs:float"/>
            <xs:element ref="Item" maxOccurs="unbounded"/>
         </xs:sequence>
         <xs:attribute name="Number" type="xs:string"/>
         <xs:attribute name="Date" type="xs:date"/>
      </xs:complexType>
   </xs:element>

   <xs:element name="Customer">
      <xs:complexType>
         <xs:sequence>
            <xs:element name="Name" type="xs:string"/>
            <xs:element name="Address" type="xs:string"/>
            <xs:element name="Phone1" type="xs:string"/>
            <xs:element name="Phone2" type="xs:string"/>
            <xs:element name="Fax" type="xs:string"/>
            <xs:element name="Email" type="xs:string"/>
            <xs:element ref="Order" maxOccurs="unbounded"/>
         </xs:sequence>
         <xs:attribute name="ID" type="xs:string"/>
      </xs:complexType>
   </xs:element>
</xs:schema>