0

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.

1 Answer 1

1

Change PearticularID to ParticularID and SubmitAmount to SubmittedAmount.

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('(ParticularID)[1]', 'int') AS 'ParticularID',
    NDS.DT.value('(Description)[1]', 'varchar(200)') AS 'Description',
    NDS.DT.value('(SubmittedAmount)[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 
    @xml.nodes('/NewDataSet/DataTable') AS NDS(DT)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.