Post reply

Update XML string passed as a parameter to SP

  • September 17, 2002 at 4:30 am

    #158904

    Hi,

    I am passing XML string as a parameter to the stored procedure. This XML string parameter contains multiple records which I need to commit to the database. I am reading this XML string (using OPENXML ) in a cursor, looping through the cursor and updating the respective records.

    Now there may be a chance that when updating multiple records, some records may not get updated (due to some reason). In such cases, I need to know which updates where sucessful and which failed. So I want the status of each record update. Is there any way by which the stored procedure is able to give back the status of each update. Which is the best method to achieve this?

    Thanks in advance.

    Ash

  • October 3, 2002 at 7:21 am

    #437727

    You need to identify the event that will flag an insertion as a success or a failure. This could be by using @@RowCount (telling you how many records were affected by the last transaction) or by a timestamp in the table(s) you are updating. The best way to do this is to create a table that will serve as a log for your XML work. For every XML attempt, write a record to that log having something like the following fields: EventID, Success/failure_Flag, TimeDate_of_Activity, and the XML string. Periodically, you can into this table, reinserting previously failed attempts. This log will also help you to develop error/event handling to avoid 'insert failures' in your original code.

    I don't really use XML, but do alot of SP writing. Are you collecting data out on the web and then sending it to a DB? I'll need to do that in the near future and could use some pointers.

    ciao ciao


    ciao ciao

  • October 12, 2002 at 4:43 am

    #438406

    What sort of reasons might they not be updated?

    Why are using a cursor. you should be able to dump the data from the xml document into a table variable and then perform the update from that.

    You could have an extra column which update with whatever means you have to determine failure. You can then return you data as a recordset or an xml file (using FOR XML)

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply