在SQL Server中将XML作为参数传递给存储过程

Question

我正在尝试从C#代码向SQL Server的存储过程传递XML参数，但它不起作用。

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 -> ", pSiteCode);
        }
    }

但是，如果没有调用存储过程，表格上就不会执行任何操作。我已经使用虚拟的XML字符串测试了存储过程，并且它可以正常工作，但是当我从C#代码传递参数时，它却无法正常工作。

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 && lSqlConnection.State != ConnectionState.Closed)
                lSqlConnection.Close();
        }
    }

我搜索并发现这是传递xml参数的正确方法。

那么我做错了什么？

谢谢。

编辑：

我找到了问题。所有建议的方法都是正确的，但由于xml区分大小写，我发现我传递的xml节点不匹配。

- user728630

---

当你进行调试时会发生什么？有任何异常吗？ - shree.pat18

没有异常，ExecuteNonQuery返回值为-1。我想知道这是否是将xml传递给存储过程的方法。 - user728630

<POLines> <POLine po_nbr="4200081264" po_line_nbr="10" ran="1054713" factory_name_code="19" /> </POLines>......................当我直接将其传递给SP时，没有问题。它可以正常工作。问题是当我从代码中传递时，什么也不发生。 - user728630

嗯，请问您能否编辑您的问题并添加SqlHelper.ExecuteNonQuery的定义呢？ - shree.pat18

你尝试将SqlParameter类型更改为SqlDbType.varchar而不是SqlDbType.Xml了吗？我认为可能SqlDbType.Xml期望以某种其他方式传递xml而不是字符串。 - TamerM

Answer 1

尝试传递 XML 值 with SqlXml。然后它应该可以工作。

- st4hoo

---

尝试过这种方式 - SqlParams[0].Value = new SqlXml(new XmlTextReader(xmlDocument.InnerXml, XmlNodeType.Document, null)); 但不起作用。 - user728630

Answer 2

您需要将变量xml作为SqlXml类的实例传递。您可以按照以下方式执行：

SqlParams[0].Value = new SqlXml(new MemoryStream(Encoding.UTF8.GetBytes(xml)));

- shree.pat18

---

我认为我传递的数据是作为 XML 属性而不是元素。这可能是原因吗？ - user728630

但是你说直接传递该字符串时它可以正常工作，对吗？ - shree.pat18