XML CRUD SQL Bulk Operation
//NOTE: Input XML file, Resulting SQL Data Operation Insert, Modify, etc.
---------------
USE [DatabaseName]
GO
/****** Object: StoredProcedure [DatabaseName].[recurring_account_transaction_add] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================
-- Description: Bulk operation to add recurring transaction records for
-- transitioning subscribers from System1 to System2
-- =====================================================================
CREATE PROCEDURE [DatabaseName].[recurring_account_transaction_add]
(@Rts xml)
AS
BEGIN
SET NOCOUNT ON;
-- declare table
create table #table(account_id int, merchant_category_code char(4), amount money, expiration_date datetime)
INSERT INTO #table(account_id, merchant_category_code, amount, expiration_date)
SELECT rt.value('@account_id','int') account_id
,rt.value('@merchant_category_code','char(4)') merchant_category_code
,rt.value('@amount','money') amount
,rt.value('@expiration_date','datetime') expiration_date
FROM @Rts.nodes('/rtList/rt') p(rt)
-- insert new recurring transaction records
insert into DatabaseName.recurring_account_transaction(account_id, merchant_category_code, amount, expiration_date)
select account_id, merchant_category_code, amount, expiration_date from #table
drop table #table
END
-------------------------
USE [DatabaseName]
GO
/****** Object: StoredProcedure [DatabaseName].[account_health_plan_sync_modify] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Description: Bulk operation to add/update/delete subscribers health
-- plan information from 3 systems
-- ====================================================
CREATE procedure [DatabaseName].[account_health_plan_sync_modify]
(@Hps xml,
@EmployerID int)
AS
BEGIN
set nocount on;
create table #table(employer_id int, account_id int, member_id int, insurance_plan_name varchar(50), system2_plan_name varchar(50), vendor_plan_name varchar(50), [type] char(3), vendor_id int)
insert into #table(employer_id, account_id, member_id, insurance_plan_name, system2_plan_name, vendor_plan_name, [type], vendor_id)
select p.hp.value('@employer_id','int') employer_id
,p.hp.value('@account_id','int') account_id
,p.hp.value('@member_id','int') member_id
,p.hp.value('@insurance_plan_name','varchar(50)') insurance_plan_name
,p.hp.value('@system2_plan_name','varchar(50)') system2_plan_name
,p.hp.value('@vendor_plan_name','varchar(50)') vendor_plan_name
,p.hp.value('@type','char(3)') [type]
,p.hp.value('@vendor_id','int') vendor_id
from @hps.nodes('hpList/hp') p(hp)
merge DatabaseName.account_health_plan_sync as s
using #table as t
on (s.employer_id = t.employer_id
and s.member_id = t.member_id
and s.vendor_id = t.vendor_id
and s.[type] = t.[type])
when not matched by source and s.employer_id = @EmployerID then
delete
when
matched and (s.insurance_plan_name<>t.insurance_plan_name or s.system2_plan_name<>t.system2_plan_name or s.vendor_plan_name<>t.vendor_plan_name)
then update set s.insurance_plan_name=t.insurance_plan_name, s.system2_plan_name=t.system2_plan_name, s.vendor_plan_name=t.vendor_plan_name, modify_date=getdate()
when
not matched then
insert (employer_id, account_id, member_id, insurance_plan_name, system2_plan_name, vendor_plan_name, [type], vendor_id)
values (t.employer_id, t.account_id, t.member_id, t.insurance_plan_name, t.system2_plan_name, t.vendor_plan_name, t.[type], t.vendor_id );
END
----------------------------------------------
AccountHealthPlanSynchronizeList
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System2.Common;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace System1
{
public class AccountHealthPlanSynchronizeList : BusinessListBase<AccountHealthPlanSynchronizeList, AccountHealthPlanSynchronizeObject>
{
#region constructors
public AccountHealthPlanSynchronizeList()
: base(DatabaseType.System1)
{
}
#endregion
#region xml
public void SaveWithXml(int employerId)
{
XElement hpList = new XElement("hpList",
from h in this
select new XElement("hp",
new XAttribute("employer_id", h.EmployerId),
new XAttribute("account_id", h.AccountId),
new XAttribute("member_id", h.MemberId),
new XAttribute("System1_plan_name", h.System1PlanName),
new XAttribute("System2_plan_name", h.System2PlanName),
new XAttribute("vendor_plan_name", h.VendorPlanName),
new XAttribute("type", h.Type),
new XAttribute("vendor_id", h.VendorId)
));
using (SqlConnection cn = new SqlConnection(Context.ConnectionString))
{
cn.OpenWithContext();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = System2.Common.Database.CommandTimeout();
cmd.CommandText = "DatabaseName.account_health_plan_sync_modify";
cmd.Parameters.Add("@Hps", System.Data.SqlDbType.Xml);
cmd.Parameters["@Hps"].Value = hpList.ToString();
cmd.Parameters.AddWithValue("@EmployerId", employerId);
cmd.ExecuteNonQuery();
}
}
}
#endregion
}
}
