0

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

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 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 :

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 Answer 1

1

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

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.