StringBuilder xmlValue = new StringBuilder();
xmlValue.Append("<?xml version=\"1.0\" ?>");
xmlValue.AppendFormat("<{0}>", "Root");
xmlValue.AppendFormat("<{0}>", "HIRA_HAZARD_ACTIVITIES_DET");
xmlValue.AppendFormat("<HazardIdentity>{0}</HazardIdentity>", ddlHazard.SelectedValue);
xmlValue.AppendFormat("<HazardRisk>{0}</HazardRisk >", ddlRisk.SelectedValue);
xmlValue.AppendFormat("<HazardCondition>{0}</HazardCondition>", codition);
xmlValue.AppendFormat("<ApplicableRequire>{0}</ApplicableRequire>", objcom.ConvertToXMLFormat(txtApplicable.Text.Trim()));
xmlValue.AppendFormat("<ipconcern>{0}</ipconcern>", ipconcern);
xmlValue.AppendFormat("<ExistControl>{0}</ExistControl>", ddlExstingControl.SelectedValue);
xmlValue.AppendFormat("<RiskSev>{0}</RiskSev>", ddlSev.SelectedValue);
xmlValue.AppendFormat("<RiskPoc>{0}</RiskPoc>", ddlPoc.SelectedValue);
xmlValue.AppendFormat("<RiskEval>{0}</RiskEval>", ddlLMHE.SelectedValue);
xmlValue.AppendFormat("<EliminationContl>{0}</EliminationContl>", ddlElimination.SelectedValue);
xmlValue.AppendFormat("<AddlContl>{0}</AddlContl>", ddlAdditional.SelectedValue);
xmlValue.AppendFormat("<AssesSev>{0}</AssesSev>", ddlsev1.SelectedValue);
xmlValue.AppendFormat("<AssesPoc>{0}</AssesPoc>", ddlpoc1.SelectedValue);
xmlValue.AppendFormat("<AssesContl>{0}</AssesContl>", ddlLMHE1.SelectedValue);
xmlValue.AppendFormat("<Flg>{0}</Flg>", hdnflagUpdate.Value);
xmlValue.AppendFormat("<RowId>{0}</RowId>", Convert.ToInt16(objcom.ConvertToInt_EmptyString(hdnRowId.Value)));
xmlValue.AppendFormat("</{0}>", "HIRA_HAZARD_ACTIVITIES_DET");
xmlValue.AppendFormat("</{0}>", "Root");
Create PROCEDURE USP_InsertUpdate(@Plant NVARCHAR (10), @Functional NVARCHAR (10), @SubFunctional NVARCHAR (10), @DocNo NVARCHAR (20), @RevNo bigint, @CreatedBy NVARCHAR (10), @ActivityRefNo NVARCHAR (10), @xmlValue XML,@Remarks nvarchar(max), @Flag NVARCHAR (5))
AS
BEGIN
SET NOCOUNT ON
DECLARE @doc INT
EXEC sp_xml_preparedocument @doc OUTPUT, @xmlValue
SELECT
tm.HazardIdentity,
tm.HazardRisk,
tm.HazardCondition,
tm.ApplicableRequire,
tm.ipconcern,
tm.ExistControl,
tm.RiskSev ,
tm.RiskPoc,
tm.RiskEval,
tm.EliminationContl,
tm.AddlContl,
tm.AssesSev,
tm.AssesPoc,
tm.AssesContl ,
tm.Flg, tm.RowId
into #Temp_HIRA_HAZARD_ACTIVITIES_DET
FROM OPENXML (@doc, 'Root/HIRA_HAZARD_ACTIVITIES_DET', 1)
WITH (
HazardIdentity VARCHAR(MAX) 'HazardIdentity/text()',
HazardRisk VARCHAR(MAX) 'HazardRisk/text()',
HazardCondition VARCHAR(MAX) 'HazardCondition/text()',
ApplicableRequire VARCHAR(MAX) 'ApplicableRequire/text()',
ipconcern VARCHAR(MAX) 'ipconcern/text()',
ExistControl VARCHAR(MAX) 'ExistControl/text()',
RiskSev VARCHAR(MAX) 'RiskSev/text()',
RiskPoc VARCHAR(MAX) 'RiskPoc/text()',
RiskEval VARCHAR(MAX) 'RiskEval/text()',
EliminationContl VARCHAR(MAX) 'EliminationContl/text()',
AddlContl VARCHAR(MAX) 'AddlContl/text()',
AssesSev VARCHAR(MAX) 'AssesSev/text()',
AssesPoc VARCHAR(MAX) 'AssesPoc/text()',
AssesContl VARCHAR(MAX) 'AssesContl/text()' ,
Flg VARCHAR(MAX) 'Flg/text()' ,
RowId VARCHAR(MAX) 'RowId/text()'
) as tm
INSERT INTO HIRA_HAZARD_ACTIVITIES_DET(DocNo,ActRefNo,HazardIdentity,HazardRisk,HazardCondition,ApplicableRequire,ipconcern,ExistControl,RiskSev,RiskPoc,RiskEval,EliminationContl,AddlContl,AssesSev,AssesPoc,AssesContl,isActive,createBy,createDate)
(SELECT
@DocNo,
@hdrRefNoActivity,
tm.HazardIdentity,
tm.HazardRisk,
tm.HazardCondition,
tm.ApplicableRequire,
tm.ipconcern,
tm.ExistControl,
tm.RiskSev ,
tm.RiskPoc,
tm.RiskEval,
tm.EliminationContl,
tm.AddlContl,
tm.AssesSev,
tm.AssesPoc,
tm.AssesContl,
'Y',
@CreatedBy,
getdate() from
#Temp_HIRA_HAZARD_ACTIVITIES_DET as tm WHERE tm.Flg='N' and tm.RowId = '0'
) ;
UPDATE
HIRA_HAZARD_ACTIVITIES_DET
SET
HazardIdentity=tm.HazardIdentity,HazardRisk=tm.HazardRisk,HazardCondition=tm.HazardCondition,ApplicableRequire=tm.ApplicableRequire,IPConcern=tm.IPConcern,
ExistControl=tm.ExistControl,RiskSev=tm.RiskSev,RiskPoc=tm.RiskPoc,RiskEval=tm.RiskEval,EliminationContl=tm.EliminationContl,AddlContl=tm.AddlContl,
AssesSev=tm.AssesSev,AssesPoc=tm.AssesPoc,AssesContl=tm.AssesContl,modifyBy=@CreatedBy,modifyDate=getdate()
FROM
#Temp_HIRA_HAZARD_ACTIVITIES_DET as tm
WHERE
-- @hdrRefNoActivity=HIRA_HAZARD_ACTIVITIES_DET.ActRefNo AND
HIRA_HAZARD_ACTIVITIES_DET.isActive='Y' AND tm.Flg='Y' AND HIRA_HAZARD_ACTIVITIES_DET.RowId =tm.RowId
END
SET NOCOUNT OFF;
END
