从XML向SQL Server表进行Upsert

Question

我试图在C#中创建一个小的控制台应用程序，以最快的方式根据XML文件的内容执行对SQL Server 2008中Products（ITEMS）表的插入。我已经有了包含适当映射到SQL表的.XSD文件（根据下面概述的方法可能不必要）。

以下是我的高级方法：

1. 读取XML，并使用它创建一个表格。
2. 使用从XML文件创建的表格对ITEMS表执行MERGE。
   2a. 如果该项存在，则更新它。
   2b. 如果该项不存在，则将其插入。
3. 仅记录插入到XML中的记录日志。

考虑以下ITEMS表和XML文件：

ITEMS

  Item_Id    Name    Price  
     1       Coke     5.00  
     2       Pepsi    3.00  
     3       Sprite   2.00   

ITEMS.XML

   <?xml version="1.0" encoding="ISO-8859-1"?>
   <Item>
    <Id>5</Id>
    <Name>Mountain Dew</Name>
    <Price>4.50</Price>
   </Item>
   <Item>
    <Id>3</Id>
    <Name>Sprite Zero</Name>
    <Price>1.75</Price>
   </Item>

在导入后，ITEMS表应该看起来像这样: ITEMS

  Item_Id    Name         Price  
     1       Coke          5.00  
     2       Pepsi         3.00  
     3       Sprite Zero   1.75  
     5       Mountain Dew  4.50

完成这一步之后，我还需要生成一个XML格式的日志文件，其中包含插入到表中的“新”记录（ITEMS_LOG.XML）：

   <?xml version="1.0" encoding="ISO-8859-1"?>
   <Item>
    <Id>5</Id>
    <Name>Mountain Dew</Name>
    <Price>4.50</Price>
   </Item>

我尝试使用SQLXMLBulkLoad进行实现，但不幸的是它没有提供我所需的日志记录，也不能让我访问从SQL Server返回的任何消息（即已插入/已更新的内容）。尽管我具有中级水平的SQL专业知识，但在这种情况下，我对使用XML还很新。非常感谢任何帮助/指导！

- TelJanini

---

你卡在哪了？能发一下你的代码吗？ - Zaki

你确定在两个不同的 items.xml 文件中，如果一个产品与数据库中已有的产品（按名称/价格）重复，它将具有相同的ID吗？ - ChrisW

@ChrisW 不，它们不一定具有相同的ID。同一项可能具有两个不同的ID。 - TelJanini

@Sam1 目前，我卡在如何将数据实际导入SQL Server上。我不知道是否应该使用XML在C#中填充数据集，还是尝试使用我的C#应用程序作为TSQL命令的包装器来执行导入。在这种情况下，速度非常重要。 - TelJanini

“items.xml” 文件通常有多大？几 KB、几 MB 还是 GB？ - Amit Naidu

Answer 1

你可以使用merge与output将数据合并到一个表变量中，然后查询该表变量以构建日志XML。 将其放在存储过程中，其中项XML作为输入参数，日志XML作为输出参数。

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
  );

在SE-Data上有一个关于IT技术的工作样例。

- Mikael Eriksson

---

@GR7 你可能会发现这个链接有些帮助。https://dev59.com/QHLYa4cB1Zd3GeqPZpcn#16773592 - Mikael Eriksson

再次感谢 @Mikael！虽然这对我有用，但当我在应用程序中尝试使用它处理XML数据的数量时，它花费了7分钟。这对我来说不是很可接受。我正在研究使用SqlBulkCopy将XML复制到临时表中，并从那里应用MERGE。你有什么建议吗？ - GR7

@GR7 从未使用过 SqlBulkCopy。 - Mikael Eriksson

Answer 2

希望这能对您有所帮助，我所做的是创建以下存储过程。基本上，该存储过程接受 xml 值并检查从代码传递的标志，确定是插入还是更新：

DECLARE @xml xml
SET @xml = @xmlCredentials

SELECT
      item.value('@Id', 'int') As ID,
      item.value('@AgentID', 'int') As AgentID,
      item.value('@Username', 'varchar (50)') As Username,
      item.value('@Password', 'varchar (50)') As [Password],
      item.value('@IsDirty', 'bit') As IsDirty,
      item.value('@IsDeleted', 'bit') As IsDeleted
INTO #tmp
FROM @xml.nodes('Credentials/Credential') x(item)

BEGIN TRY
BEGIN TRAN
      INSERT INTO Credentials (AgentID, Username, [Password])
          SELECT
              AgentID, Username, [Password]
          FROM
              #tmp
          WHERE
              ID = 0 AND IsDirty = 1
      UPDATE c
      SET c.[AgentID] = t.AgentID,
          c.[Username] = t.Username,
          c.[Password] = t.[Password]
      FROM
          [dbo].[Credentials] c
      JOIN 
          #tmp t ON t.Id = c.ID
      WHERE
          t.IsDirty = 1 AND t.IsDeleted = 0

      DELETE FROM [dbo].[Credentials]
      FROM [dbo].[Credentials] c
      JOIN #tmp t ON t.Id = c.ID
      WHERE 
          t.IsDirty = 1 AND t.IsDeleted = 1

      COMMIT TRAN
END TRY
BEGIN CATCH

      IF @@TRANCOUNT > 0
            ROLLBACK TRAN

      DECLARE @errorMSG varchar(4000)
      DECLARE @errorSeverity int
      DECLARE @errorState int

      SET @errorMSG = ERROR_MESSAGE()
      SET @errorSeverity = ERROR_SEVERITY()
      SET @errorState = ERROR_STATE()

      RAISERROR (@errorMSG,
                        @errorSeverity, @errorState);

END CATCH

SELECT [ID], [AgentID], [Username], [Password]
FROM [dbo].[Credentials]

在代码后台，我有我的xml并将xml作为参数传递给存储过程：

// read xml and assign it to string variable
string xml = readxml();

try
{
    string command = "EXEC SaveCredentails '" + xml + "'";
}
catch(Exception e)
{
}

- Zaki

Answer 3

我会使用一个暂存表将XML导入到SQL Server表中。添加一个额外的列来指示操作（插入或更新）。然后像往常一样使用常规SQL执行upserts。然后，您可以使用暂存表生成所需的XML日志（读取操作列以确定是插入还是更新）。

- TyT