XML query error in SQL Server 2005

Question

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.HouseRentPaid = 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

Did you try to debug this code? How it selects the xml? Does items from EmployeeFinYearInvestment.Id = ox.Id; clause exist? — VMAtm
– VMAtm, Commented Jul 7, 2011 at 13:15

marc_s · Accepted Answer · 2011-07-07 13:25:43Z

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');

Collectives™ on Stack Overflow

XML query error in SQL Server 2005

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related