December 12, 2007 at 7:52 am
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
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
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