使用 IBM DB2 Visual Studio 2005 .Net 插件进行 XML 数据验证

                
CREATE TABLE CARPOOL (
	ID INTEGER NOT NULL, 
	FIRSTNAME VARCHAR ( 256 ) , 
	LASTNAME VARCHAR ( 256 ) , 
	TITLE VARCHAR ( 15 ) , 
	PHONE VARCHAR ( 20 ) , 
	CARPOOLINFO XML , 
	CONSTRAINT CARPOOL_PK PRIMARY KEY (ID))
      

                
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:include id="USAddress.xsd" schemaLocation="USAddress.xsd" />
  <xs:element name="CARPOOLINFO">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="StartTime" type="xs:string" />
        <xs:element name="Address" type="USAddress" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
      

                
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:complexType name="USAddress">
    <xs:sequence>
      <xs:element name="Add1" type="xs:string">
      </xs:element>
      <xs:element name="Add2" type="xs:string">
      </xs:element>
      <xs:element name="City" type="xs:string">
      </xs:element>
      <xs:element name="State" type="xs:string">
      </xs:element>
      <xs:element name="Zip" type="xs:string">
      </xs:element>
    </xs:sequence>
  </xs:complexType>
</xs:schema>
      

                
        private DB2Connection m_conn = null;
        m_conn = new DB2Connection("database=sample");
        m_conn.Open();
        

                
       //We have two xsds, CarpoolInfo.xsd and USAddress.xsd,
       //where CarpoolInfo.xsd is dependent on USAddress.xsd.
       //This XML Schema is identified as schema.xsdname.

      String[] xsdFiles = new String[2]
      {
          "CarpoolInfo.xsd",
          "USAddress.xsd"                
            
      };

      String[] schemaLocs = new String[2]
      {   
        "http://tempuri.org/CarpoolInfo.xsd",
        "USAddress.xsd"                
            
      };
           

      // Register XML schema using DB2 .Net provider connection’s 
      // RegisterXmlSchema API 
      m_conn.RegisterXmlSchema(schema,
                         xsdname,
                         schemaLocs,
                         xsdFiles,
                         null,
                         null,
                         false);
         

                
      // Update the database based on the user's action in the dataGrid.
      // Performs INSERT, UPDATE and DELETE.
      private void update()
        {
            mydataSourceDT = carpoolInfo.DataSource as DataTable;
            dtChanges = mydataSourceDT.GetChanges();
            if (dtChanges == null)
                return;

            // Need to generate insert/update/delete commands to //validate against
            // carpoolinfo.xsd
            DB2Command insert = new DB2Command
               ("INSERT INTO CARPOOL" +
                "(FIRSTNAME,LASTNAME,TITLE,PHONE,CARPOOLINFO)" +
                " VALUES(?,?,?,?," +
                "XMLVALIDATE(xmlparse (DOCUMENT CAST" +
                " ( ? as CLOB) PRESERVE WHITESPACE )" +
                "ACCORDING TO XMLSCHEMA ID " + schema + ".CARPOOLINFO ))");

            DB2Command update = new DB2Command
              ("UPDATE  CARPOOL SET FIRSTNAME=?,LASTNAME=?,TITLE=?,PHONE=?," + 
              "CARPOOLINFO=XMLVALIDATE(xmlparse (DOCUMENT CAST " +
              "( ? as CLOB) PRESERVE WHITESPACE ) " +
              "ACCORDING TO XMLSCHEMA ID " + schema + ".CARPOOLINFO ) WHERE ID=?");

            DB2Command delete = new DB2Command
               ("DELETE FROM CARPOOL WHERE ID=?");

            //Add the Parameters and bind them to the DataTable's //corresponding columns.
            DB2Parameter fn1 = new DB2Parameter("fn1", DB2Type.VarChar);
            DB2Parameter fn2 = new DB2Parameter("fn2", DB2Type.VarChar);            
            fn1.SourceColumn = "FIRSTNAME";
            fn2.SourceColumn = "FIRSTNAME";            
            insert.Parameters.Add(fn1);
            update.Parameters.Add(fn2);

            DB2Parameter ln1 = new DB2Parameter("ln1", DB2Type.VarChar);
            DB2Parameter ln2 = new DB2Parameter("ln2", DB2Type.VarChar);            
            ln1.SourceColumn = "LASTNAME";
            ln2.SourceColumn = "LASTNAME";
            insert.Parameters.Add(ln1);
            update.Parameters.Add(ln2);

            DB2Parameter tl1 = new DB2Parameter("tl1", DB2Type.VarChar);
            DB2Parameter tl2 = new DB2Parameter("tl2", DB2Type.VarChar);            
            tl1.SourceColumn = "TITLE";
            tl2.SourceColumn = "TITLE";            
            insert.Parameters.Add(tl1);
            update.Parameters.Add(tl2);

            DB2Parameter ph1 = new DB2Parameter("ph1", DB2Type.VarChar);
            DB2Parameter ph2 = new DB2Parameter("ph2", DB2Type.VarChar);           
            ph1.SourceColumn = "PHONE";
            ph2.SourceColumn = "PHONE";            
            insert.Parameters.Add(ph1);
            update.Parameters.Add(ph2);

            DB2Parameter info1 = new DB2Parameter("info1", DB2Type.Clob);
            DB2Parameter info2 = new DB2Parameter("info2", DB2Type.Clob);
            info1.SourceColumn = "CARPOOLINFO";
            info2.SourceColumn = "CARPOOLINFO";            
            insert.Parameters.Add(info1);
            update.Parameters.Add(info2);

            DB2Parameter i1 = new DB2Parameter("i1", DB2Type.Integer);
            i1.SourceColumn = "ID";         
            update.Parameters.Add(i1);
            DB2Parameter i2 = new DB2Parameter("i2", DB2Type.Integer);
            i2.SourceColumn = "ID";  
            delete.Parameters.Add(i2);              

            da.InsertCommand = insert;
            da.UpdateCommand = update;
            da.DeleteCommand = delete;
         
            // Perform the update.
            da.Update(dtChanges);
            mydataSourceDT.AcceptChanges();

            // Refill the dataset, refresh the dataGridView.
            ds.Clear();
            da.Fill(ds, xsdname);
        }
       

                
      // Populate the dataGrid.
      // If "All" is selected, all data from the table will be displayed.
      // if a city's name is selected, only rows whose CARPOOLINFO contain
      // <City>cityName</City> will be displayed.        

      public void populate(String cityName)
        {
            if ( cityName.Equals("All"))
            {
                carpoolInfo.DataSource = ds.Tables[xsdname];
            }
            else
            {
                xq = "select * from CARPOOL where " +
                 "xmlexists('declare namespace 
                 def=\"http://tempuri.org/XMLSchema.xsd\";" +
                 
                 "$c/def:CARPOOLINFO/def:Address[def:City=" + "\"" + cityName +
                 "\"]' passing CARPOOL.CARPOOLINFO as \"c\")" +
                 "order by ID";
                DB2DataAdapter da1 = new DB2DataAdapter(xq, m_conn);
                DataSet ds1 = new DataSet();
                da1.Fill(ds1, xsdname);
                carpoolInfo.DataSource = ds1.Tables[xsdname];                
            }
        }