Upsert on SQL Server table from XML

Question

I'm attempting to create a small console app in C# to perform inserts on a table of Products (ITEMS) in SQL Server 2008 according to the contents of an XML file in the FASTEST way possible. I already have an .XSD file that contains the proper mappings to the SQL table (which may not be necessary with the approach outlined below). Here's a high-level of my approach: - Read the XML, using it to create a table. - Perform a MERGE against the ITEMS table using the table created from the XML file. 2a. If the item exists, update it. 2b. If the item does not exist, insert it. - Create a log of only the records inserted in XML. Consider the following ITEMS table and XML file: ITEMS ``` Item_Id Name Price 1 Coke 5.00 2 Pepsi 3.00 3 Sprite 2.00 ``` ITEMS.XML ``` 5 Mountain Dew 4.50 3 Sprite Zero 1.75 ``` After the import, the ITEMS table should look…

Accepted Answer

You can use `merge` with `output` to a table variable and then query the table variable to build the log XML.

Put it in a stored procedure where you have the item XML as an in parameter and the log XML as an out parameter.

```
create procedure AddItemXML
  @ItemsXML xml,
  @ItemsLogXML xml out
as

declare @Changes table
(
  Item_Id int,
  Name nvarchar(20),
  Price money,
  Action nvarchar(10)
);

merge Items as T
using
  (
    select T.N.value('Id[1]', 'int') as Item_Id,
           T.N.value('Name[1]', 'varchar(20)') as Name,
           T.N.value('Price[1]', 'money') as Price
    from @ItemsXML.nodes('/Item') T(N)
  ) as S
on T.Item_Id = S.Item_Id
when matched then
  update set Name = S.Name, Price = S.Price
when not matched then
  insert (Item_Id, Name, Price) values (S.Item_Id, S.Name, S.Price)
output inserted.Item_Id,
       inserted.Name,
       inserted.Price,
       $action 
  into @Changes;

set @ItemsLogXML = 
  (
    select Item_Id as ID,
           Name,
           Price
    from @Changes
    where Action = 'INSERT'
    for xml path('Item'), type
  );
```

Working sample on SE-Data

Upsert on SQL Server table from XML

Solution

Problem

Related problems