xml插入表或更新表

USE [accountmanager]
GO
/****** Object:  StoredProcedure [dbo].[PersonInfoAddByXmlProc]    Script Date: 2019/5/9/周四 10:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		hsb
-- Create date: 20190509
-- Description:	新员工门店分配导入
-- =============================================
Alter PROCEDURE [dbo].[PersonExpoerToShopUPByXmlProc]
	@xmlStrHeGe xml, 
 	@xmlStrBuHeGe xml, 
 	@xmlStrjson xml, 
	@add_per int ,

	@out NVARCHAR(100) OUTPUT
AS
BEGIN 
	BEGIN transaction
	BEGIN TRY
	BEGIN
		DECLARE @handle INT;  
		DECLARE @PrepareXmlStatus INT;  
		DECLARE @count INT;   
		DECLARE @guid NVARCHAR(64)
		SET @guid=replace(NEWID(), '-', '')
		EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @xmlStrHeGe;  
		IF ( 1=1)
		BEGIN

			UPDATE F SET F.per_dept=C.id,F.per_shop=D.SHOPID,F.per_busi=E.Roleid
			FROM  OPENXML(@handle, '/ArrayOfPersonImportModel/PersonImportModel', 2)  
			    WITH (
			    身份证 NVARCHAR(20),
			    姓名 NVARCHAR(20),
			    区域 NVARCHAR(200),
			    分店 NVARCHAR(200),
			    职位 NVARCHAR(200)
			    ) A
				INNER JOIN [dbo].[Base_Department_Tbl] C ON A.区域=C.deptName and ISNULL( c.del,0)=0
				LEFT JOIN [dbo].[Base_Shop_Tbl] D ON A.分店=D.Shopname and ISNULL( d.del,0)=0
				INNER JOIN [dbo].[Base_RoleList] E ON A.职位=E.RoleName and ISNULL( e.IsDel,0)=0
				LEFT Join [dbo].[Base_Person_Tbl] F ON A.身份证=F.per_idcard  AND F.DEL=0

				SET @out =[OperationDB].[dbo].[GetProcOutValFunc](1,'导入成功！')
				--导入日志
				INSERT INTO [dbo].[PersonImportLogTbl](jsonHege,jsonBuHege,json,addper)
				VALUES(@xmlStrHeGe,@xmlStrBuHeGe,@xmlStrjson,@add_per)

				--时间轴
				INSERT INTO [EmployeeTimeline](perId,newDept,newShop,newArea,opType,opTime,newPosition)
		        SELECT b.id,b.per_dept,b.per_shop,c.parentID,3,getdate(),b.per_busi 
		        from [Base_Person_Tbl] b 
		        left join [dbo].[Base_Department_Tbl] c on b.per_dept=c.id
		        where b.[route]=@guid

				--插入条数
				SET @count=(select COUNT(1) from [dbo].[Base_Person_Tbl] where [route]=@guid)
				update [dbo].[Base_Person_Tbl] set per_code=id where [route]=@guid
				SET @out =[OperationDB].[dbo].[GetProcOutValFunc](@count,'导入成功！')
		END
	END

	
		
		
	
	
	COMMIT TRANSACTION 
	END TRY
	BEGIN CATCH 
		SET @out =[OperationDB].[dbo].[GetProcOutValFunc](-1,'操作失败，服务器内部错误,请联系管理员！[-2]！')
		ROLLBACK TRANSACTION
		INSERT INTO LogDB.DBO.SysProErrLogTbl values(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE(),GETDATE())
	END CATCH
	
END

　　

USE [accountmanager]
GO
/****** Object:  StoredProcedure [dbo].[PersonInfoAddByXmlProc]    Script Date: 2019/5/9/周四 10:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		hsb
-- Create date: 20181112
-- Description:	人事表导入
-- =============================================
ALTER PROCEDURE [dbo].[PersonInfoAddByXmlProc]
	@xmlStrHeGe xml, 
 	@xmlStrBuHeGe xml, 
 	@xmlStrjson xml, 
	@add_per int ,

	@out NVARCHAR(100) OUTPUT
AS
BEGIN 
	BEGIN transaction
	BEGIN TRY
	BEGIN
		DECLARE @handle INT;  
		DECLARE @PrepareXmlStatus INT;  
		DECLARE @count INT;   
		DECLARE @guid NVARCHAR(64)
		SET @guid=replace(NEWID(), '-', '')
		EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @xmlStrHeGe;  
		IF ( 1=1)
		BEGIN
			INSERT INTO [Base_Person_Tbl](per_idcard,per_name,per_sex,per_age,per_birthday,per_tel,per_knowWay,per_know,per_exper,per_height,per_email,per_ancientFrom,per_nation,per_address,urgentConnectionPer,urgentPhone,per_dept,per_shop,per_busi,per_job,per_graduateTime,industryYears,per_marry,add_per,add_date,per_school,[route]) 
			SELECT A.身份证,A.姓名
			,CASE A.性别  WHEN '男' THEN 1 WHEN '女' THEN 0 else '2' END 性别
			,year(getdate())-CONVERT(int, substring(A.身份证,7,4)) 年龄
			,CONVERT(datetime, substring(A.身份证,7,8)) 出生日期
			,A.电话号码
			,CASE A.入职渠道  WHEN '网络招聘' THEN 1 WHEN '校园招聘' THEN 2 WHEN '上门招聘' THEN 3 WHEN '转介绍' THEN 4 WHEN '其他途径' THEN 5 else 0 END 入职渠道
			,CASE A.学历  WHEN '博士' THEN 1 WHEN '硕士' THEN 2 WHEN '本科' THEN 3 WHEN '大专' THEN 4 WHEN '中专' THEN 7 WHEN '高中' THEN 5 WHEN '初中及以下' THEN 6 else 0 END 学历
			,A.专业,A.身高,A.邮箱,A.籍贯,A.民族,A.居住地,A.紧急联系人,A.紧急联系人电话,C.id,ISNULL( D.shopID,0) shopID,E.RoleID
			,A.入职时间+' 00:00:00.000' 入职时间
			,A.毕业时间+' 00:00:00.000' 毕业时间
			,CONVERT(int, A.行业年限) 行业年限
			,CASE A.婚姻状况  WHEN '已婚' THEN 1 WHEN '未婚' THEN 0 else '2' END 婚姻状况
			,@add_per 添加人
			,GETDATE() 添加时间
			,A.毕业学校
			,@guid 来路
			FROM  OPENXML(@handle, '/ArrayOfPersonImportModel/PersonImportModel', 2)  
			    WITH (
			    身份证 NVARCHAR(20),
			    姓名 NVARCHAR(20),
			    性别 NVARCHAR(20),
			    电话号码 NVARCHAR(20),
			   -- 大区 NVARCHAR(20),
			    区域 NVARCHAR(200),
			    分店 NVARCHAR(200),
			    职位 NVARCHAR(200),
			    入职渠道 NVARCHAR(20),
			    学历 NVARCHAR(20),
			    专业 NVARCHAR(20),
			    身高 NVARCHAR(20),
			    邮箱 NVARCHAR(20),
			    籍贯 NVARCHAR(20),
			    民族 NVARCHAR(100),
			    居住地 NVARCHAR(600),
			    紧急联系人 NVARCHAR(20),
			    紧急联系人电话 NVARCHAR(20),
			    入职时间 NVARCHAR(20),
			    毕业时间 NVARCHAR(20),
			    行业年限 NVARCHAR(20),
			    婚姻状况 NVARCHAR(20),
			    毕业学校 NVARCHAR(200)
			    ) A
				--LEFT JOIN [dbo].[Base_Department_Tbl] B ON A.大区=B.deptName
				inner JOIN [dbo].[Base_Department_Tbl] C ON A.区域=C.deptName and ISNULL( c.del,0)=0
				left JOIN [dbo].[Base_Shop_Tbl] D ON A.分店=D.Shopname and ISNULL( d.del,0)=0
				inner JOIN [dbo].[Base_RoleList] E ON A.职位=E.RoleName and ISNULL( e.IsDel,0)=0
				where A.身份证 not in (select per_idcard from [dbo].[Base_Person_Tbl] where del!=2) 

				SET @out =[OperationDB].[dbo].[GetProcOutValFunc](1,'导入成功！')
				--导入日志
				INSERT INTO [dbo].[PersonImportLogTbl](jsonHege,jsonBuHege,json,addper)
				VALUES(@xmlStrHeGe,@xmlStrBuHeGe,@xmlStrjson,@add_per)

				--时间轴
				INSERT INTO [EmployeeTimeline](perId,newDept,newShop,newArea,opType,opTime,newPosition)
		        SELECT b.id,b.per_dept,b.per_shop,c.parentID,3,getdate(),b.per_busi 
		        from [Base_Person_Tbl] b 
		        left join [dbo].[Base_Department_Tbl] c on b.per_dept=c.id
		        where b.[route]=@guid

				--插入条数
				SET @count=(select COUNT(1) from [dbo].[Base_Person_Tbl] where [route]=@guid)
				update [dbo].[Base_Person_Tbl] set per_code=id where [route]=@guid
				SET @out =[OperationDB].[dbo].[GetProcOutValFunc](@count,'导入成功！')
		END
	END

	
		
		
	
	
	COMMIT TRANSACTION 
	END TRY
	BEGIN CATCH 
		SET @out =[OperationDB].[dbo].[GetProcOutValFunc](-1,'操作失败，服务器内部错误,请联系管理员！[-2]！')
		ROLLBACK TRANSACTION
		INSERT INTO LogDB.DBO.SysProErrLogTbl values(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE(),GETDATE())
	END CATCH
	
END