Post reply

OENXML and UPDATE

  • December 12, 2007 at 7:52 am

    #62897

    Hello all,

    I am using SQL Server 2000 and OPENXML to insert and update multiple tables in a database. The xml string is passed into a stored proc.

    Inserts are straightforward. Updates are more of a challenge. I won't know until runtime which of many optional elements will be present in the xml for record updates and I want to update only the data in these elements.

    Can anyone suggest a general approach for this?

    Thanks!

    Jonathan

  • December 19, 2007 at 2:56 am

    #761321

    Could you post an example? (DDL, sample data, expected results.)

    Anyway, unless you know the XML Schema in advance you're pretty much in a world of trouble. Then again, why wouldn't you know the schema in advance...?

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • December 19, 2007 at 8:26 am

    #761424

    I do know the schema; the issue is that many of the elements are otional and if they are not supplied I don' want to overwrite the old value with null in an UPDATE. I ended up doing the following:

    UPDATE dbo.tblPax

    SET

    PaxPassportNumber = CASE WHEN shred.Number IS NULL THEN PaxPassportNumber ELSE shred.Number END,

    PaxPassportExpirationDate = CASE WHEN shred.ExpirationDate IS NULL THEN PaxPassportExpirationDate ELSE shred.ExpirationDate END,

    PaxPassportCountry = CASE WHEN shred.Country IS NULL THEN PaxPassportCountry ELSE shred.Country END

    FROM

    OPENXML (@iTree, 'BatchObject/BatchObjectPackage/Application/Student/Passport',2)

    WITH

    (

    Number varchar(50),

    ExpirationDate datetime,

    Country varchar(2)

    ) shred

    WHERE

    PaxID = @PaxID

    I am checking for null, if null use the old value.

    Thanks for your reply!

    Jonathan

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

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