Append XML data to SQL Server 2000

Append XML data to SQL Server 2000

• john_roborodent

  You could do something like this:

  // The source XML data:
  StringBuilder sb = new StringBuilder();
  sb.Append("<MyTable>");
  sb.Append("<row>");
  sb.Append("<Col1>data 1 ...</Col1>");
  sb.Append("</row>");
  sb.Append("<row>");
  sb.Append("<Col1>data 2 ...</Col1>");
  sb.Append("</row>");
  sb.Append("<row>");
  sb.Append("<Col1>data 3 ...</Col1>");
  sb.Append("</row>");
  sb.Append("</MyTable>");

  XmlDataDocument xmldoc = new XmlDataDocument();
  xmldoc.LoadXml(sb.ToString());

  // Now go through each row and append (insert) to SQL Server:
  String strConn = "Connection String...";
  using (SqlConnection sqlConn = new SqlConnection(strConn))
  {
      StringBuilder strSQL = new StringBuilder("IF NOT EXISTS(SELECT * FROM MyTable WHERE Col1 = @Col1) ");
      strSQL.Append("INSERT INTO MyTable (Col1) VALUES (@Col1)");
      using (SqlCommand sqlCmd = new SqlCommand(strSQL.ToString(), sqlConn))
      {
          sqlCmd.CommandType = CommandType.Text;
          sqlCmd.Parameters.Add("@Col1", SqlDbType.VarChar, 255);
          sqlConn.Open();
          foreach (XmlNode node in xmldoc.GetElementsByTagName("row"))
          {
              sqlCmd.Parameters["@Col1"].Value = node["Col1"].InnerText;
              int intRecs = sqlCmd.ExecuteNonQuery();
              if (intRecs < 1)
                  throw new ApplicationException("Unable to insert row.");
          }
          sqlConn.Close();
      }
  }

  
  
  
• Append XML data to SQL Server 2000