October 3, 2011 at 5:29 am
Hi,
I have a third part application that send some data in xml format that has to be inserted in the database. The xml looks like:
<Items>
<Item a="23" b="first item"/>
<Item a="15" b="an item"/>
<Item a="78" b="item 2"/>
.............................
</Items>
Now this xml is parsed by my application and each item is saved by calling a stored procedure that save an item:
spSaveItem @a as int, b as nvarchar(40)
If the saving of an item failed (due some check constraints, etc), the error is logged into application and the saving continue with the next item. The problem is that the xml is very big (more than 10000 items) and the procedure is called thousands of times and creates performance problems.
I create a stored procedure that take as parameter the whole xml and save it:
spSaveItems @x as xml
BEGIN
INSERT INTO dbo.Items
SELECT T.c.value('@a', 'int') AS a
T.c.value('@b', 'nvarchar') AS b
FROM @x.nodes('Items/Item') AS T(c)
END
but this does not work as I wish. I want that stored procedure does not fail if an item fails to be inserted. I want that the error somehow to be logged and the insertion to continue with the next item and at the finish of the sp an error to be raised with all items that fails.
Thanks,
ioani
October 3, 2011 at 5:34 am
I am not trying to be rude, but if that is the behaviour you want, you should have a look at MySQL. SQL Server is by nature ACID compliant, meaning that a single statement either succeed or fail. So, if one row fails to insert, the whole statement fails, causing other modifications by that statement to be rolled back. As far as I know (and hope), there is no way having SQL Server what you want to do.
October 3, 2011 at 6:18 am
I tend to disagree with Ole Kristian:
SQL Server can handle such scenarios. The concept is called "staging table". You'll shred the xml into a separate table (e.g. all columns being varchar(200)), perform the logic test to detect invalid values and insert the rest.
October 3, 2011 at 6:19 am
This was removed by the editor as SPAM
October 3, 2011 at 9:54 am
Ok, I understand that I made myself misunderstood. Of course you can use a staging table, subqueries, common table expressions and so forth to insert the rows not conflicting with constriants. What you cannot do however, is overriding the ACID compliance of SQL Server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply