XML query error in SQL Server 2005

For updating a mass records, I used xml query. From the front end (C#.net) I populate xml and pass it to a stored procedure as a parameter (like @Master_rows_xml_Update).

I face a situation.. when I go for this command in the stored procedure

declare @i INTEGER,@Master_rows_xml_Update XML;

SET  @Master_rows_xml_Update= '<root>   
                                 <row Id="1" FinYearId="5" EmployeeId="55" EnteredOn="7/1/2011 12:00:00 AM" EnteredBy="1" HouseRentPaid="False" HouseRentAmount="3500.00" officeId="9"/>
                                 <row Id="2" FinYearId="5" EmployeeId="55" EnteredOn="7/1/2011 12:00:00 AM" EnteredBy="1" HouseRentPaid="False" HouseRentAmount="3500.00" officeId="9"/>
                                </root>';

exec sp_xml_preparedocument @i output, @Master_rows_xml_Update;

update EmployeeFinYearInvestment
    set EmployeeFinYearInvestment.FinYearId = ox.FinYearId,
    EmployeeFinYearInvestment.EmployeeId = ox.EmployeeId,
    EmployeeFinYearInvestment.EnteredOn = ox.EnteredOn,
    EmployeeFinYearInvestment.EnteredBy = ox.EnteredBy,
    EmployeeFinYearInvestment.HouseRentPa开发者_如何学运维id = ox.HouseRentPaid,
    EmployeeFinYearInvestment.HouseRentAmount = ox.HouseRentAmount,
    EmployeeFinYearInvestment.officeId=ox.officeId

from OpenXml(@i, 'root/row')
    with (Id INT,FinYearId INT, EmployeeId INT,EnteredOn DATETIME,EnteredBy INT , HouseRentPaid BIT,HouseRentAmount DECIMAL ,officeId INT ) ox
    where EmployeeFinYearInvestment.Id = ox.Id;

exec sp_xml_removedocument @i;

..IT CAN NOT UPDATE ACTION TO FOLLOWING TABLE...

Procedure also not returning any error...

ANY IDEA...

Thanks.. Anirban

I would try to get rid of OpenXml and those function - use the new XQuery/XPath stuff in SQL Server 2005 !

Try this - this should give you the values from your XML:

DECLARE @XmlUpdate XML 
SET @XmlUpdate  = 
  '<root>   
   <row Id="1" FinYearId="5" EmployeeId="55" EnteredOn="7/1/2011 12:00:00 AM" EnteredBy="1" HouseRentPaid="False" HouseRentAmount="3500.00" officeId="9"/>
   <row Id="2" FinYearId="5" EmployeeId="55" EnteredOn="7/1/2011 12:00:00 AM" EnteredBy="1" HouseRentPaid="False" HouseRentAmount="3500.00" officeId="9"/>
  </root>';

SELECT
    UpdRow.value('(@Id)[1]', 'int') AS 'ID',
    UpdRow.value('(@FinYearId)[1]', 'int') AS 'FinYearID',
    UpdRow.value('(@EmployeeId)[1]', 'int') AS 'EmployeeID',
    UpdRow.value('(@EnteredOn)[1]', 'datetime') AS 'EnteredOn',
    UpdRow.value('(@EnteredBy)[1]', 'int') AS 'EnteredBy',
    UpdRow.value('(@HouseRentPaid)[1]', 'bit') AS 'RentPaid',
    UpdRow.value('(@HouseRentAmount)[1]', 'decimal(20,4)') AS 'RentAmount',
    UpdRow.value('(@OfficeId)[1]', 'int') AS 'OfficeID'
FROM 
    @XmlUpdate.nodes('/root/row') AS R(UpdRow)

And of course, if you can select it, you can also use it to update!

UPDATE 
    dbo.EmployeeFinYearInvestment
SET
    EmployeeFinYearInvestment.FinYearId = UpdRow.value('(@FinYearId)[1]', 'int'),
    EmployeeFinYearInvestment.EmployeeId = UpdRow.value('(@EmployeeId)[1]', 'int'),
    ..... (and so on). ......
    EmployeeFinYearInvestment.EnteredOn = ox.EnteredOn,
FROM 
    @XmlUpdate.nodes('/root/row') AS R(UpdRow)
WHERE
    EmployeeFinYearInvestment.Id = UpdRow.value('(@Id)[1]', 'int');