在C＃中从存储过程插入XML

I am trying to insert xml from a stored procedure into a table.

我试图将存储过程中的xml插入表中。

Not able to do it.

不能这样做。

My approach :

我的方法:

CREATE PROCEDURE [dbo].[InsertXML]
    @xml XML
AS
BEGIN
      SET NOCOUNT ON;

      INSERT INTO [GpsCorporateCardIncentive](CCFrom, CCTo, CCIncentive)
         SELECT
             [Table].[Column].value('From[1]','DECIMAL(5,3)') AS CCFrom, 
             --ATTRIBUTE
             [Table].[Column].value('To[1]','DECIMAL(5,3)') AS CCTo, 
             --TAG
             [Table].[Column].value('IncentiveAmount[1]','DECIMAL(5,3)') AS CCIncentive --TAG
        FROM
            @xml.nodes('/ArrayOfIncentive/Invcentive') as [Table]([Column])
END

My xml looks like this:

我的xml看起来像这样:

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfIncentive xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Incentive>
    <From>65.000</From>
    <To>89.000</To>
    <IncentiveAmount>25.000</IncentiveAmount>
  </Incentive>
  <Incentive>
    <From>67.000</From>
    <To>90.000</To>
    <IncentiveAmount>25.000</IncentiveAmount>
  </Incentive>
</ArrayOfIncentive>

C# Code :

C#代码:

public static void SaveXML(string filePath)
{
    string xml = File.ReadAllText(filePath);           

    using (SqlConnection con = new SqlConnection(GetConnectionString()))
    {
        con.Open();

        SqlCommand cmd = new SqlCommand("InsertXML", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@xml", xml);                

        cmd.ExecuteNonQuery();
    }
}

But I don't see changes getting inserted in database.

但我没有看到更改插入数据库中。

I am not getting any error either.

我也没有收到任何错误。

Please help.

1 个解决方案

#1

@xml.nodes('/ArrayOfIncentive/Invcentive') as Table

@ xml.nodes('/ ArrayOfIncentive / Invcentive')如表所示

It can be a spelling error: see, you have Invcentive in FROM statement, but in your XML you don't have such tags.

它可能是拼写错误:请参阅,您在FROM语句中有Invcentive,但在XML中,您没有这样的标记。