Example: Loading an XML Document into a Table

Teradata Vantageā„¢ XML Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1140-162K

These INSERT statements load an XML document into the customer and customerText tables:

INSERT INTO customer (1, 'John Hancock', CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
   <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>'));
INSERT INTO customerText (1, 'John Hancock', '<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
   <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>');