将XML数据批量插入SQL Server 2005数据库的最佳方法

Question

我需要将大约25-30个XML文件插入到我的SQL Server 2005数据库中(总大小约为10 MB)。当新的XML文件被复制到服务器上时，我需要此逻辑自动运行。

我已经阅读了该网站和其他网站上的许多文章，但仍无法确定应使用什么来解析数据。请告诉我应该选择哪个选项:

1. SqlBulk Copy 2. C# 反序列化 3. SSIS

我必须为我的数据模型创建C#类。因此，C＃反序列化是我的首选。但请告诉我从性能角度来看应该选择哪个选项。

还有一件事我忘记提及：XML文件的结构会有所不同。它不会完全相同。我将拥有表，其中将具有可能填充的所有列。但有时候，XML文件并不会包含所有数据。

XML样本：

<?xml version="1.0" encoding="utf-8"?>
<estateList date="2012-08-06T12:17:05">
  <uniqueID>22XXln</uniqueID>
  <category name="Apartment" /> 
  <listingAgent>
     <name>DIW Office</name>  
     <telephone type="BH">96232 2345</telephone> 
     <telephone type="BH">9234 2399</telephone>
     <email>[e-mail]</email>    
  </listingAgent>
  <inspectionTimes /> 
  <description>AVAILABLE NOW. </description> 
  <price>0</price>  
  <address display="yes">      
    <street>Lachlsan Street</street>        
    <ImagesContainer>        
       <img id="m" modTime="2012-08-06-12:17:05" url="http://images/2409802.jpg" format="jpg" /> 
       <img id="a" modTime="2012-08-06-12:17:05" /> 
    </ImagesContainer>     
  </address>
</estateList>

谢谢。

- Deefa

---

我们的数据库在 Sql server 2005 上。我编写的逻辑将需要在每次新的 XML 文件被放置到该位置时运行。 - Deefa

可能是每10天一次，甚至更频繁。 - Deefa

嗨，马克，我有一个名为estateList的表格，每个元素都是我的列。我需要日期的值，公寓名称，上市经纪人的姓名（DIW办事处）。 - Deefa

你如何处理多个<telephone>或<img>元素？ - marc_s

我在数据库中有多个电话列。对于img，我必须将唯一ID和图像URL的值插入到另一个SQL表中。 - Deefa

Answer 1

如果你已经将XML存储在SQL变量中，你可以很容易地使用SQL Server 2005中添加的XQuery支持直接解析出大部分信息。

尝试类似以下的语句：

DECLARE @Input XML = '<estateList date="2012-08-06T12:17:05">
  <uniqueID>22XXln</uniqueID>
  <category name="Apartment" /> 
  <listingAgent>
     <name>DIW Office</name>  
     <telephone type="BH">96232 2345</telephone> 
     <telephone type="BH">9234 2399</telephone>
     <email>[e-mail]</email>    
  </listingAgent>
  <inspectionTimes /> 
  <description>AVAILABLE NOW. </description> 
  <price>0</price>  
  <address display="yes">      
    <street>Lachlsan Street</street>        
    <ImagesContainer>        
       <img id="m" modTime="2012-08-06-12:17:05" url="http://images/2409802.jpg" format="jpg" /> 
       <img id="a" modTime="2012-08-06-12:17:05" /> 
    </ImagesContainer>     
  </address>
</estateList>'

SELECT
    EstateListDate = EstL.value('@date', 'datetime'),
    UniqueID = EstL.value('(uniqueID)[1]', 'varchar(20)'),
    Category = EstL.value('(category/@name)[1]', 'varchar(20)'),
    ListingAgentName = EstL.value('(listingAgent/name)[1]', 'varchar(50)'),
    ListingAgentTel = EstL.value('(listingAgent/telephone)[1]', 'varchar(50)'),
    ListingAgentEMail = EstL.value('(listingAgent/email)[1]', 'varchar(250)'),
    [Description] = EstL.value('(description)[1]', 'varchar(250)'),
    Price = EstL.value('(price)[1]', 'decimal(14,2)'),
    DisplayAddress = EstL.value('(address/@display)[1]', 'varchar(10)'),
    AddressStreet = EstL.value('(address/street)[1]', 'varchar(100)')
FROM @input.nodes('/estateList') AS Tbl(EstL)

你应该得到：

这些数据可以很容易地插入到表格中。并且可以使用相当简单的SSIS包对磁盘上的任意数量的XML文件运行此查询（枚举XML，将每个加载到SQL变量中，解析它，将数据插入表格等）。

但是： 需要解决的问题是：

• 是否可能有多个房产代理？如果是：如何处理？
• 是否可能有多个电话号码？如何处理？
• 如何处理每个地址的多个图像？

等等....

更新：此查询将从XML输入中提取UniqueID和每个完整的<img>标记的信息，并显示它（或将其插入到另一个表格中）：

SELECT
    UniqueID = @input.value('(/estateList/uniqueID)[1]', 'varchar(20)'),
    ImageID = Images.value('(img/@id)[1]', 'varchar(20)'),
    ImageModTime = Images.value('(img/@modTime)[1]', 'varchar(50)'),
    ImageFormat = Images.value('(img/@format)[1]', 'varchar(20)'),
    ImageURL = Images.value('(img/@url)[1]', 'varchar(250)')
FROM 
    @input.nodes('/estateList/address/ImagesContainer') AS Tbl(Images)

- marc_s

---

非常感谢，马克。我会再检查几个XML文件然后回来。但是感谢您给了我方向。所以您的意思是我要针对一个SSIS包编写此查询。 - Deefa

@Deefa：编写这个查询（或多个查询），然后在 SSIS 包中执行——我认为这似乎是最明智的方法。该 SSIS 包可以安排在每周一凌晨 4 点运行，或者根据您的需要进行调整..... - marc_s