title: Pass xml as a parameter to a stored procedure in SQL Server
body: <p>I am trying to pass an XML parameter from C# code to a stored procedure in SQL Server but it doesn't work.</p>
  
  <pre><code>public static void SaveReceiptTrans(string pSiteCode, string xml)
  {
          try
          {
              string DBKey = "sn";
              string ConnStr = Encryption.DecryptString(ConfigurationManager.ConnectionStrings[DBKey].ConnectionString);
              string CmdStr = "prc_pt_Fac_iu";
  
              SqlParameter[] SqlParams = new SqlParameter[1];
  
              SqlParams[0]            = new SqlParameter("@pReceiptsWithFactoryNameCode", SqlDbType.Xml);
              SqlParams[0].Direction  = ParameterDirection.Input;
              SqlParams[0].Value      = xml;
  
              int i = SqlHelper.ExecuteNonQuery(ConnStr, CommandType.StoredProcedure, CmdStr, SqlParams);
          }
          catch (Exception ex)
          {
              UtilLogging.LogException(ex, "Error From -&gt; ", pSiteCode);
          }
      }
  </code></pre>
  
  <p>But no operations are done on tables through stored procedure as if there is no call made to the stored procedure. I have tested the stored procedure with dummy xml string and it works fine but when pass the parameter from c# code it doesn't work.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[prc_pt_Fac_iu]
  (
      @pReceiptsWithFactoryNameCode XML
  )
  BEGIN
     SET NOCOUNT ON
  
     BEGIN TRY    
        DECLARE @MAXTRANSID INT, @MAXMOVEID INT, @ROWCOUNT INT, @NEXTTRANSID INT, @NEXTMOVEID INT
        DECLARE @IDOC INT
        DECLARE @TEMPCOUNT INT, @INTFLAG INT
  
        SELECT 
           IDENTITY(INT) AS id, 
           line.item.value('@site_cde', 'char(8)') AS site_cde,  
           line.item.value('@po_nbr', 'char(17)') AS po_nbr,  
           line.item.value('@po_line_nbr', 'smallint') AS po_line_nbr, 
           line.item.value('@ran', 'int') AS ran, 
           line.item.value('@factory_name_code', 'int') AS factory_name_code, 
           NULL AS item_id, 
           NULL AS qty,
           NULL AS cur_loc_id,
           NULL AS cur_loc_status,
           NULL AS trans_id,
           NULL AS [user_id]
        INTO 
           #tmpReceiptsWithFactoryNameCode  
        FROM 
           @pReceiptsWithFactoryNameCode.nodes('/POLines/POLine') AS line(item) 
  
        SELECT  
           @MAXTRANSID = COALESCE(MAX(trans_id),0)
        FROM 
           PartsTrack_Receipt_Trans (NOLOCK) 
        WHERE 
           trans_id IS NOT NULL
  
        UPDATE rt 
        SET trans_id = @MAXTRANSID + temp.id,
            factory_name_code = temp.factory_name_code
        FROM PartsTrack_Receipt_Trans_BKP rt 
        INNER JOIN #tmpReceiptsWithFactoryNameCode temp ON rt.receipt_ack_nbr = temp.ran 
                                                        AND rt.po_nbr = temp.po_nbr 
                                                        AND rt.po_line_nbr = temp.po_line_nbr
  END
  
  public static int ExecuteNonQuery(string pConnectionString, CommandType pCommandType, string pCommandText, SqlParameter[] pSqlParameters)
  {
          SqlConnection lSqlConnection = null;
  
          try
          {
              lSqlConnection = new SqlConnection(pConnectionString);
  
              SqlCommand lSqlCommand = new SqlCommand();
              lSqlCommand.CommandType = pCommandType;
              lSqlCommand.Connection = lSqlConnection;
              lSqlCommand.CommandText = pCommandText;
              lSqlCommand.CommandTimeout = 180;
  
              if (pSqlParameters != null)
              {
                  foreach (SqlParameter lSqlParameter in pSqlParameters)
                      lSqlCommand.Parameters.Add(lSqlParameter);
              }
  
              lSqlConnection.Open();
  
              return lSqlCommand.ExecuteNonQuery();
          }
          catch (SqlException sqlExp)
          {
              throw sqlExp;
          }
          catch (Exception exp)
          {
              throw exp;
          }
          finally
          {
              if (lSqlConnection != null &amp;&amp; lSqlConnection.State != ConnectionState.Closed)
                  lSqlConnection.Close();
          }
      }
  </code></pre>
  
  <p>I searched and found that this is the correct way to pass the xml parameter.</p>
  
  <p>Then what am I doing wrong?</p>
  
  <p>Thanks</p>
  
  <p>EDITED:</p>
  
  <p>I found the issue. All the suggested methods were right but since xml is case sensitive I found the mis-match in xml node i was passing. </p>