I am getting error in updating columns using XML but don't know where is the problem. Please guide me to get the rid out of it.. This is part of the code for my stored procedure
IF(@Mode='UPDATE_TABLE')
BEGIN
;WITH XmlData AS
(
SELECT
NDS.DT.value('(ClaimExpenseID)[1]', 'int') AS 'ClaimExpenseID',
NDS.DT.value('(ClaimID)[1]', 'int') AS 'ClaimID',
NDS.DT.value('(POrderID)[1]', 'int') AS 'POrderID',
NDS.DT.value('(SiteID)[1]', 'int') AS 'SiteID',
NDS.DT.value('(FromDate)[1]', 'datetime') AS 'FromDate',
NDS.DT.value('(ToDate)[1]', 'datetime') AS 'ToDate',
NDS.DT.value('(PearticularID)[1]', 'int') AS 'ParticularID',
NDS.DT.value('(Description)[1]', 'varchar(200)') AS 'Description',
NDS.DT.value('(SubmitAmount)[1]', 'int') AS 'SubmittedAmount',
NDS.DT.value('(CreatedDate)[1]', 'datetime') AS 'CreatedDate',
NDS.DT.value('(ApprovedAmount)[1]', 'int') AS 'ApprovedAmount',
NDS.DT.value('(ApprovedDate)[1]', 'datetime') AS 'ApprovedDate',
NDS.DT.value('(Remark)[1]', 'varchar(300)') AS 'Remark',
NDS.DT.value('(ApprovedBy)[1]', 'int') AS 'ApprovedBy',
NDS.DT.value('(RowInfo)[1]', 'varchar(20)') AS 'RowInfo'
FROM
@xmlString.nodes('/NewDataSet/DataTable') AS NDS(DT)
)
MERGE INTO dbo.ClaimExpenseTRS CET
USING XmlData x ON CET.ClaimExpenseID = x.ClaimExpenseID
WHEN MATCHED AND x.RowInfo = 'UPDATE'
THEN
UPDATE SET
CET.ClaimID=x.ClaimID,
CET.CreatedDate=x.CreatedDate,
CET.POrderID=x.POrderID,
CET.SiteID=x.SiteID,
CET.FromDate=x.FromDate,
CET.ToDate=x.ToDate,
CET.ParticularID=x.ParticularID,
CET.Description=x.Description,
CET.SubmittedAmount=x.SubmittedAmount,
CET.ApprovedAmount=x.ApprovedAmount,
CET.Remarks=x.Remark,
CET.ApproveBy=x.ApprovedBy,
CET.ApprovedDate=x.ApprovedDate
WHEN MATCHED AND x.RowInfo = 'DELETE'AND CET.ClaimExpenseID = x.ClaimExpenseID
THEN DELETE
WHEN NOT MATCHED AND x.RowInfo = 'NEW'
THEN
INSERT(ClaimID, CreatedDate, POrderID, SiteID,FromDate,ToDate,ParticularID, Description,SubmittedAmount,ApprovedAmount,Remarks,ApproveBy,ApprovedDate)
VALUES(x.ClaimID,x.CreatedDate,x.POrderID,x.SiteID,x.FromDate,x.ToDate,x.
ParticularID,x.Description,x.SubmittedAmount,x.ApprovedAmount,x.Remark,x. ApprovedBy,x.ApprovedDate);
END
This is my XML script..
<NewDataSet>
<DataTable>
<ClaimExpenseID>14</ClaimExpenseID>
<ClaimID>10</ClaimID>
<CreatedDate>2011-09-14T02:00:00+05:30</CreatedDate>
<POrderID>11</POrderID>
<SiteID>1</SiteID>
<FromDate>2011-09-18T00:00:00+05:30</FromDate>
<ToDate>2011-09-20T00:00:00+05:30</ToDate>
<NoOfDays>2</NoOfDays>
<ParticularID>1</ParticularID>
<Description>Restaurant123 </Description>
<SubmittedAmount>200</SubmittedAmount>
<Month>September</Month>
<Year>2011</Year>
<POrderNo>UNINOR</POrderNo>
<SiteName>ALKAPURI</SiteName>
<ParticulerName>Food</ParticulerName>
<RowInfo>UPDATE</RowInfo>
<TableRowIndex>0</TableRowIndex>
</DataTable>
<DataTable>
<ClaimExpenseID>15</ClaimExpenseID>
<ClaimID>10</ClaimID>
<CreatedDate>2011-09-15T00:00:00+05:30</CreatedDate>
<POrderID>10</POrderID>
<SiteID>2</SiteID>
<FromDate>2011-09-16T00:00:00+05:30</FromDate>
<ToDate>2011-09-17T00:00:00+05:30</ToDate>
<NoOfDays>1</NoOfDays>
<ParticularID>2</ParticularID>
<Description>bus</Description>
<SubmittedAmount>100</SubmittedAmount>
<Month>September</Month>
<Year>2011</Year>
<POrderNo>PO-00120</POrderNo>
<SiteName>NIZAMPURA</SiteName>
<ParticulerName>Traveling123</ParticulerName>
<RowInfo>UNCHANGED</RowInfo>
<TableRowIndex>1</TableRowIndex>
</DataTable>
When I run this script, it stores NULL for particularID and for SubmittedAmount both.