Insertion of rows with XML column values

To insert rows into a table that contains XML columns, you can use the SQL INSERT statement.

The documents that you insert into XML columns must be well-formed XML documents, as defined in the XML 1.0 specification. A document node will be created implicitly if one does not already exist. The application data type can be XML (XML AS BLOB, XML AS CLOB, XML AS DBCLOB), character, or binary.

Recommendation: Insert XML data from host variables, rather than literals, so that the Db2 database server can use the host variable data type to determine some of the encoding information.

XML data in an application can be in textual XML format or binary XML format (Extensible Dynamic Binary XML Db2 Client/Server Binary XML Format). Binary XML format is valid only for JDBC, SQLJ, and ODBC applications. When you insert the data into an XML column, it must be converted to its XML hierarchical format. The Db2 database server performs this operation implicitly when XML data is inserted directly from a host variable into an XML column. Alternatively, you can invoke the XMLPARSE function explicitly when you perform the insert operation, to convert the data to the XML hierarchical format.

During document insertion, you can validate the XML document against a registered XML schema. If the XML column into which you are inserting a document has an XML schema modifier, validation occurs automatically. Otherwise, you can call the DSN_XMLVALIDATE function to do XML schema validation. You can perform validation during document insertion only if the document is in the textual XML format.

The following examples demonstrate how XML data can be inserted into XML columns. The examples use table MYCUSTOMER, which is a copy of the sample CUSTOMER table. The XML data that is to be inserted looks like this: 
<customerinfo xmlns="http://posample.org" Cid="1015">
<name>Christine Haas</name>
<addr country="Canada">
<street>12 Topgrove</street>
<city>Toronto</city>
<prov-state>Ontario</prov-state>
<pcode-zip>N8X-7F8</pcode-zip>
</addr>
<phone type="work">905-555-5238</phone>
<phone type="home">416-555-2934</phone>
</customerinfo>
Example: In a JDBC application, read textual XML data from file c6.xml as binary data, and insert the data into an XML column: 
PreparedStatement insertStmt = null;
String sqls = null;
int cid = 1015;
sqls = "INSERT INTO MyCustomer (Cid, Info) VALUES (?, ?)";
insertStmt = conn.prepareStatement(sqls);
insertStmt.setInt(1, cid);
File file = new File("c6.xml");
insertStmt.setBinaryStream(2, new FileInputStream(file), (int)file.length());
insertStmt.executeUpdate();
Example: Suppose that the data in file c7.xml contains the same XML document as in file c6.xml, but the data is stored in binary XML format. In a JDBC application, read the data from file c7.xml, and insert the data into an XML column: 
…
SQLXML info = conn.createSQLXML();
OutputStream os = info.setBinaryStream();
FileInputStream fis = new FileInputStream("c7.xml");
int read;
while ((read = fis.read ()) != -1) {
  os.write (read);
}
PreparedStatement insertStmt = null;
String sqls = null;
int cid = 1015;
sqls = "INSERT INTO MyCustomer (Cid, Info) VALUES (?, ?)";
insertStmt = conn.prepareStatement(sqls);
insertStmt.setInt(1, cid);
insertStmt.setSQLXML(2, info);
insertStmt.executeUpdate();
Example: In a static embedded C application, insert data from an XML AS BLOB host variable into an XML column: 
EXEC SQL BEGIN DECLARE SECTION;
   sqlint64 cid;
   SQL TYPE IS XML AS BLOB (10K) xml_hostvar;
EXEC SQL END DECLARE SECTION;
…
cid=1015;
/* Read data from file c6.xml into xml_hostvar */
…
EXEC SQL INSERT INTO MyCustomer (Cid,Info) VALUES (:cid, :xml_hostvar);
Example: In a static embedded COBOL application, insert data from a character XML host variable into an XML column:
…
 WORKING-STORAGE SECTION.                                  
…
* XML HOST VARIABLES
 01  CLOB-XML-IN USAGE IS SQL TYPE IS XML AS CLOB(10K).    
 01  CLOB-XML-OUT USAGE IS SQL TYPE IS XML AS CLOB(10K).   
* VARIABLE USED FOR DISPLAY OF THE RETRIEVED VALUE
 01  CLOB-XML-OUT-DISPLAY.                                 
   02  CLOB-XML-OUT-DISPLAY-LENGTH                         
       PIC 9(9) COMP.                                      
   02  CLOB-XML-OUT-DISPLAY-DATA.                          
       49  FILLER PIC X(10240).                            
*****************************************************     
* SQL INCLUDE FOR SQLCA                             *     
*****************************************************     
      EXEC SQL INCLUDE SQLCA  END-EXEC.                   
 PROCEDURE DIVISION.                                      
* USE XMLPARSE TO CONVERT THE INPUT DATA TO THE XML TYPE.
      EXEC SQL SET :CLOB-XML-IN=                            
       XMLPARSE(DOCUMENT                                        
       '<customerinfo xmlns="http://posample.org" Cid="1015">' ||
       '<name>Christine Haas</name>'                           ||
       '<addr country="Canada">'                               ||
       '<street>12 Topgrove</street>'                          ||
       '<city>Toronto</city>'                                  ||
       '<prov-state>Ontario</prov-state>'                      ||
       '<pcode-zip>N8X-7F8</pcode-zip>'                        ||
       '</addr>'                                               ||
       '<phone type="work">905-555-5238</phone>'               ||
       '<phone type="home">416-555-2934</phone>'               ||
       '</customerinfo>')                                        
      END-EXEC.
* INSERT THE DATA.
      EXEC SQL INSERT INTO CUSTOMER(CID, INFO)                  
       VALUES (1015,:CLOB-XML-IN)                              
      END-EXEC.                                                 
* CHECK THE VALUE THAT YOU INSERTED.
      EXEC SQL SELECT INFO                                     
       INTO :CLOB-XML-OUT FROM CUSTOMER                         
       WHERE CID=1015                                           
      END-EXEC.                                                 
      MOVE CLOB-XML-OUT TO CLOB-XML-OUT-DISPLAY.                
      DISPLAY                                                   
      CLOB-XML-OUT-DISPLAY-DATA(1:CLOB-XML-OUT-DISPLAY-LENGTH).