How to pass XMl Data to Sql server

First you have to get connection string from web.config file .

web.config file

<connectionStrings>
    <add name="AngConnection" connectionString="server=PRIYANKARA-LAP\SQL2016;database=Mvc_Sample;uid=sa;password=asd@123;" />
</connectionStrings>

C# file
public static string getConnection()
{
           return ConfigurationManager.ConnectionStrings["AngConnection"].ConnectionString;
}



Data Modell
public class InvoiceDetail
{
        public long Id { get; set; }
        public long RowId { get; set; }
        public long ItemId { get; set; }
        public string ItemCode { get; set; }
        public decimal Qty { get; set; }
}

Data Access

public string SaveInvoice(Invoice inv, List<InvoiceDetail> invDetail)
{
            SqlTransaction objTrans = null;
            SqlConnection scon = new SqlConnection(getConnection());
            scon.Open();
            objTrans = scon.BeginTransaction();
            try
            {
                string xmlInvDetais = ToXml(invDetail);

                SqlCommand scom = new SqlCommand("[dbo].[UpdateInvoice]", scon, objTrans);
                scom.CommandType = CommandType.StoredProcedure;

                SqlParameter outResult = new SqlParameter
                {
                    ParameterName = "@Result",
                    SqlDbType = SqlDbType.VarChar,
                    Size = 50,
                    Direction = ParameterDirection.Output
                };


                SqlParameter pCustomerId = new SqlParameter { ParameterName = "@CustomerId", SqlDbType = SqlDbType.BigInt, Value = inv.CustomerId };
                SqlParameter pCustomerName = new SqlParameter { ParameterName = "@CustomerName", SqlDbType = SqlDbType.VarChar, Size = -1, Value = inv.CustomerName };
                SqlParameter pInvoiceDate = new SqlParameter { ParameterName = "@InvoiceDate", SqlDbType = SqlDbType.DateTime, Value = inv.InvoiceDate };
                SqlParameter pCustomerCatId = new SqlParameter { ParameterName = "@CustomerCatId", SqlDbType = SqlDbType.Int, Value = inv.CustomerCatId };
                SqlParameter pCustomerCatDes = new SqlParameter { ParameterName = "@CustomerCatDes", SqlDbType = SqlDbType.VarChar, Size = -1, Value = inv.CustomerCatDes };
                SqlParameter pPaymethord = new SqlParameter { ParameterName = "@Paymethord", SqlDbType = SqlDbType.VarChar, Size = -1, Value = inv.Paymethord };
                SqlParameter pLoyeltyCus = new SqlParameter { ParameterName = "@LoyeltyCus", SqlDbType = SqlDbType.Bit, Value = inv.LoyeltyCus };
                SqlParameter pXmlInvDetail = new SqlParameter { ParameterName = "@XmlInvDetail", SqlDbType = SqlDbType.Xml, Value = xmlInvDetais };

                scom.Parameters.Add(pCustomerId);
                scom.Parameters.Add(pCustomerName);
                scom.Parameters.Add(pInvoiceDate);
                scom.Parameters.Add(pCustomerCatId);
                scom.Parameters.Add(pCustomerCatDes);
                scom.Parameters.Add(pPaymethord);
                scom.Parameters.Add(pLoyeltyCus);
                scom.Parameters.Add(pXmlInvDetail);
                scom.Parameters.Add(outResult);

                scom.ExecuteNonQuery();
                objTrans.Commit();
            }
            catch (Exception EX)
            {
                objTrans.Rollback();
                throw;
            }
            finally
            {
                scon.Close();
            }
            //List<ErrorMessage> messages = new List<ErrorMessage>();
            //messages = (String.IsNullOrEmpty(outParam2.Value.ToString())) ? new List<ErrorMessage>() : Utility.FromXml<List<ErrorMessage>>(outParam2.Value.ToString());

            return "";
}


Convert object to Xml
public static string ToXml<T>(T obj)
{
            using (StringWriter stringWriter = new StringWriter(new StringBuilder(), CultureInfo.InvariantCulture))
            {
                XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
                xmlSerializer.Serialize(stringWriter, obj);
                return stringWriter.ToString();
            }

}



SQl SP

ALTER PROCEDURE [dbo].[UpdateInvoice]
@CustomerId BIGINT,
@CustomerName VARCHAR(MAX),
@InvoiceDate DATETIME,
@CustomerCatId INT,
@CustomerCatDes VARCHAR(MAX),
@Paymethord VARCHAR(MAX),
@LoyeltyCus BIT,
@XmlInvDetail XML,
@Result VARCHAR(50) OUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XmlInvDetail

SELECT  [Id],
[RowId],
[ItemId],
[ItemCode],
[Qty]
INTO #Temp_SyncedIds
FROM OPENXML(@hdoc,'ArrayOfInvoiceDetail/InvoiceDetail',2)
WITH
(--Xml String contained data
[Id] BIGINT,
[RowId] BIGINT,
[ItemId] BIGINT,
[ItemCode] VARCHAR(MAX),
[Qty] DECIMAL(18,2)
)


INSERT INTO [dbo].[tblInvoiceDetail]
           ([RowId]
           ,[ItemId]
           ,[ItemCode]
           ,[Qty])
SELECT 
[RowId],
[ItemId],
[ItemCode],
[Qty]
FROM #Temp_SyncedIds
   

END