Lesson 3: Inserting XML documents into XML type columns

Well-formed XML documents are inserted into XML typed columns using the INSERT SQL statement. This lesson shows you how to insert well-formed XML documents into XML columns by using the INSERT SQL statement.

This lesson shows how to insert XML documents into XML type columns manually, by using the command-line processor. Typically, however, XML documents are inserted by using application programs.

Although you can insert XML data by using XML, binary, or character types, to avoid code page conversion issues, use XML or binary types. In this lesson, the XML documents are character literals. In most cases, you cannot directly assign string data to a target with an XML data type; you must first explicitly parse the data by using the XMLPARSE function. In INSERT, UPDATE, or DELETE operations, however, you can assign string data directly to XML columns, without an explicit call to the XMLPARSE function. In these three cases, the string data is implicitly parsed. Refer to the XML parsing documentation for more information.

To insert three XML documents into the Customer table that you created in Lesson 1, issue the following statements:

INSERT INTO Customer (Cid, Info) VALUES (1000, 
'<customerinfo xmlns="http://posample.org" Cid="1000"> 
  <name>Kathy Smith</name> 
  <addr country="Canada"> 
    <street>5 Rosewood</street> 
    <city>Toronto</city> 
    <prov-state>Ontario</prov-state> 
    <pcode-zip>M6W 1E6</pcode-zip> 
  </addr> 
  <phone type="work">416-555-1358</phone> 
</customerinfo>')~

INSERT INTO Customer (Cid, Info) VALUES (1002, 
'<customerinfo xmlns="http://posample.org" Cid="1002"> 
  <name>Jim Noodle</name> 
  <addr country="Canada"> 
    <street>25 EastCreek</street> 
    <city>Markham</city> 
    <prov-state>Ontario</prov-state> 
    <pcode-zip>N9C 3T6</pcode-zip> 
  </addr> 
  <phone type="work">905-555-7258</phone> 
</customerinfo>')~

INSERT INTO Customer (Cid, Info) VALUES (1003, 
'<customerinfo xmlns="http://posample.org" Cid="1003"> 
  <name>Robert Shoemaker</name> 
  <addr country="Canada"> 
    <street>1596 Baseline</street> 
    <city>Aurora</city> 
    <prov-state>Ontario</prov-state> 
    <pcode-zip>N8X 7F8</pcode-zip> 
  </addr> 
  <phone type="work">905-555-2937</phone> 
</customerinfo>')~

To confirm that the records were successfully inserted, issue the following statement:

SELECT * from Customer~

Return to the tutorial