title: Replace/Avoid unicode characters in XML C# while sending XML data to SQL server
body: <p>Below is the code to create XML data so that I can send the same as a string to Sql server for bulk insert.</p>
  
  <hr>
  
  <pre><code>private string SavePurchaseInvoiceProducts()
          {
              string xmldata = string.Empty;
              List&lt;ProductDetail&gt; ProductsList = new List&lt;ProductDetail&gt;();
              try
              {
                  foreach (DataGridViewRow row in gvPurchaseInvoiceItems.Rows)
                  {
  
                      if (dtBatchDetails.AsEnumerable().Count(i =&gt; i.Field&lt;int&gt;("ItemID").ToString().Equals(row.Cells["ItemID"].EditedFormattedValue.ToString())) &gt; 0)
                      {
                          ProductsList.Add(new ProductDetail
                          {
  
                              ItemID = Convert.ToInt32(row.Cells["ItemID"].EditedFormattedValue.ToString()),
                              StockInHand = Convert.ToDecimal(row.Cells["StockinHand"].EditedFormattedValue),
                              ReceivedQTY = Convert.ToDecimal(row.Cells["Qty"].EditedFormattedValue),
                              NetPrice = Convert.ToDecimal(row.Cells["NetPrice"].EditedFormattedValue),
                              SellPrice1 = Convert.ToDecimal(row.Cells["StockWas"].EditedFormattedValue),
                              Tax_ID = Convert.ToInt32(row.Cells["Tax_ID"].EditedFormattedValue.ToString()),
                              ItemDescription = row.Cells["Description"].EditedFormattedValue.ToString()
  
                          });
                          row.Cells["StockWas"].Value = Convert.ToDecimal(row.Cells["StockinHand"].EditedFormattedValue);
                      }
                      else
                      {
                          MessageBox.Show("Please complete Batch qty entry for the product " + row.Cells["Description"].EditedFormattedValue.ToString(), "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                          return "Error";
                      }
                  }
  
                  var xmlPBDetails = new XElement("Root",
                                      from pbdetail in ProductsList
                                      select new XElement("InvoiceItemDetails",
                                                     new XElement("ItemID", pbdetail.ItemID),
                                                     new XElement("ItemDesc", pbdetail.ItemDescription),
                                                     new XElement("StockinHand", pbdetail.StockInHand),
                                                     new XElement("NetPrice", pbdetail.NetPrice),
                                                     new XElement("Stock_RecievedQty", pbdetail.ReceivedQTY),
                                                     new XElement("StockInHand_Was", pbdetail.SellPrice1),
                                                     new XElement("Tax_ID", pbdetail.Tax_ID),
                                                     new XElement("StockUpdateReason", "PurchaseInvoice"),
                                                     new XElement("EmpID", GlobalData.EmpID),
                                                     new XElement("Company_ID", GlobalData.CompanyID),
                                                     new XElement("Lastupdated", String.Format("{0:MM/dd/yyyy HH:mm:ss}", DTPInvoiceDate.Value)),
                                                     new XElement("IsDeleted", 0)
                                                 ));
  
                  xmldata = xmlPBDetails.ToString();
              }
              catch (Exception ex)
              {
  
              }
              return xmldata;
          }
  </code></pre>
  
  <hr>
  
  <p>The above code works fine but the only problem is with the line
  new XElement("ItemDesc", pbdetail.ItemDescription) where ItemDescription is string</p>
  
  <p>For example if the ItemDescription contains <strong>μ</strong> (Mu) then the sql server throws an error called ' XML Parsing error an invalid character'.Same is the case with all unicode characters. IF there any Regular expression to completely avoid these characters before sending to database or any other work around.I have tried few methods 
  <a href="http://weblogs.sqlteam.com/mladenp/archive/2008/10/21/Different-ways-how-to-escape-an-XML-string-in-C.aspx" rel="nofollow">http://weblogs.sqlteam.com/mladenp/archive/2008/10/21/Different-ways-how-to-escape-an-XML-string-in-C.aspx</a> but all these only avoid few characters which we define.</p>
  
  <p>Below is the StoredProcedure</p>
  
  <pre><code> Create PROCEDURE [dbo].[inv_Save_InvoiceProductDetails] 
      @InvoiceProductDetails as varchar(Max)
      as                                                                    
  
      BEGIN                                                                                                        
      declare @i int                                                    
      exec sp_xml_preparedocument @i output,@InvoiceProductDetails     
  
      INSERT INTO  INV_ProductInvoiceDetails(ProductInvoiceID,ItemID,NetPrice,StockinHand,                                                                        
      Company_ID,Lastupdated,EmpID,IsDeleted,Stock_RecievedQty,Tax_ID,ItemDesc)                                                                            
      SELECT ProductInvoiceID,ItemID,NetPrice,StockinHand,Company_ID,getdate(),EmpID,IsDeleted,                            
      Stock_RecievedQty,Tax_ID,ItemDesc FROM OPENXML(@i,'Root/InvoiceItemDetails',2)                             
      WITH (                                                                            
      ProductInvoiceID int,ItemID int,NetPrice numeric(18,2),StockinHand numeric(18,3),Company_ID int,                                  
      Lastupdated datetime,EmpID int,IsDeleted bit,Stock_RecievedQty numeric(18,2),Tax_ID int,ItemDesc varchar(100))                                                                  
  
      exec sp_xml_removedocument @i                                                                            
  
      END 
  </code></pre>
  
  <p>So please advice on escaping these characters or any other work around to insert in the database apart from the Encoding/Decoding idea.</p>
  
  <p>Thanks.</p>