有XML文件需要它populatemultiple SQL表

Question

我有一个XML文件，我需要填充多个SQL表，我想知道什么是最好的方式来做到这一点。我在想数据集，或xslt，但我真的不知道。这是我生成的XML（的一部分）

<?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
- <!-- Created: 8/3/2010 12:09:15 PM 
    --> 
- <Trip> 
- <TripDetails> 
    <DepartureDate /> 
    <ReturnDate /> 
    <TripTypeA>3</TripTypeA> 
    <TripTypeB>1</TripTypeB> 
    <PurposeOfTrip>vacation</PurposeOfTrip> 
    <Region>5</Region> 
- <Countries> 
    <Country>105</Country> 
    <Country>135</Country> 
    </Countries> 
- <Cities> 
    <City>Cancun</City> 
    <City>Tokyo</City> 
    <City>Mayo</City> 
    </Cities> 
    <OverallRating>4</OverallRating> 
    <Suppliers>53</Suppliers> 
    <SuppliersComments>Good flight</SuppliersComments> 
- <Transport> 
    <TransportType>1</TransportType> 
    <TransportType>3</TransportType> 
    </Transport> 
    <TransportComment>Transportation was fast</TransportComment> 

我有我需要填充。几个不同的表（保持它短为例）

TripDetails (TripID, TripTypeA, TripTypeB, SupplierID, overallRating) 
TripCountries (TripCountryID, TripID, CountryCode) 

我有一大堆的多个表（市，运输），但如果我能想出如何更新TripDetails（主表）和TripCountries（这是一个汇集TripDetails和国家的表格），我想我会很好，谢谢！

Answer 1

假设您使用的是SQL Server，您应该将XML解析为DataTable，并使用SqlBulkCopy对象以超快的速度将它们投射到数据库中。有很多资源可以帮助您了解SqlBulkCopy。下面是另一个StackOverflow问题的最新讨论，以帮助您入门：Sql Server 2008 Tuning with large transactions (700k+ rows/transaction)

如果XML文件非常大，那么您应该小心使用哪种解析器。 XDocument和XmlDocument将整个事物加载到内存中。如果文件足够小，例如10MB以下，那么使用这些解析器应该没问题。

---

编辑：

这里是你如何能得到XML转换的DataTable快速实物模型。这是在VB中，因为VB使XML更容易一些。

Option Strict On : Option Explicit On : Option Infer On : Option Compare Binary 

Imports System.Data 
Imports System.Linq 
Imports System.Xml.Linq 

Module Module1 

    Sub Main() 
     Dim xml = 
     <Trip> 
      <TripDetails id="1"> 
       <DepartureDate/> 
       <ReturnDate/> 
       <TripTypeA>3</TripTypeA> 
       <TripTypeB>1</TripTypeB> 
       <PurposeOfTrip>vacation</PurposeOfTrip> 
       <Region>5</Region> 
       <Countries> 
        <Country>105</Country> 
        <Country>135</Country> 
       </Countries> 
       <Cities> 
        <City>Cancun</City> 
        <City>Tokyo</City> 
        <City>Mayo</City> 
       </Cities> 
       <OverallRating>4</OverallRating> 
       <Suppliers>53</Suppliers> 
       <SuppliersComments>Good flight</SuppliersComments> 
       <Transport> 
        <TransportType>1</TransportType> 
        <TransportType>3</TransportType> 
       </Transport> 
       <TransportComment>Transportation was fast</TransportComment> 
      </TripDetails> 
     </Trip> 

     Dim dtTripDetails As New DataTable() 
     With dtTripDetails.Columns 
      .Add("TripID", GetType(Integer)) 
      .Add("TripTypeA", GetType(Integer)) 
      .Add("DepartureDate", GetType(DateTime)) 
      .Add("TransportComment", GetType(String)) 
     End With 

     Dim dtTripDetailXrefCountries As New DataTable() 
     With dtTripDetailXrefCountries.Columns 
      .Add("TripID", GetType(Integer)) 
      .Add("CountryID", GetType(Integer)) 
     End With 

     Dim xdetails = From td In xml.Descendants("TripDetails") Select td 
     For Each xdetailRecord As XElement In xdetails 
      Dim tripID As Integer = CInt(xdetailRecord.Attribute("id").Value) 
      Dim tripTypeA As Integer = CInt(xdetailRecord.Element("TripTypeA").Value) 
      Dim strDepDate As String = xdetailRecord.Element("DepartureDate").Value 
      Dim depDate As Object = If(String.IsNullOrEmpty(strDepDate), CType(DBNull.Value, Object), CType(DateTime.Parse(strDepDate), Object)) 
      Dim transportComment As String = xdetailRecord.Element("TransportComment").Value 
      dtTripDetails.Rows.Add(tripID, tripTypeA, depDate, transportComment) 

      Dim xcountries = From c In xdetailRecord.Element("Countries").Elements("Country") Select c 
      For Each xcountryRecord As XElement In xcountries 
       Dim countryID As Integer = CInt(xcountryRecord.Value) 
       dtTripDetailXrefCountries.Rows.Add(tripID, countryID) 
      Next 
     Next 

     Console.WriteLine("Done") 
     Console.ReadKey(True) 

    End Sub 

End Module 

顺便说一句 - 当做这样的ETL时，最好先将数据抽取到登台表中，而不是直接输入到生产表中。这样，您就可以验证数据类型，并确保参照完整性并处理密钥管理，并使所有内容完美无缺地锁定或污染您的生产表。

Answer 2

您可以很容易地使用SQL Server 2005的XQuery功能，例如，有一个存储过程，关于这些XML字符串作为输入的接受：

CREATE PROCEDURE dbo.StoreTripDetails(@XmlContent XML) 
AS BEGIN 
    DECLARE @NewTripID INT 

    INSERT INTO dbo.TripDetails(TripTypeA, TripTypeB, SupplierID, overallRating) 
     SELECT 
     Trip.Details.value('(TripTypeA)[1]', 'int') 'TripTypeA', 
     Trip.Details.value('(TripTypeB)[1]', 'int') 'TripTypeB', 
     Trip.Details.value('(Suppliers)[1]', 'int') 'SupplierID', 
     Trip.Details.value('(OverallRating)[1]', 'int') 'OverallRating' 
     FROM 
     @XmlContent.nodes('/Trip/TripDetails') AS Trip(Details) 

    SELECT @NewTripID = SCOPE_IDENTITY() 

    INSERT INTO dbo.TripCountries(TripID, CountryCode) 
     SELECT 
     @NewTripID, Trip.Countries.value('(.)[1]', 'int') 
     FROM 
     @XmlContent.nodes('/Trip/TripDetails/Countries/Country') AS Trip(Countries) 
END 

，然后你可以很容易地调用从C＃代码这个存储过程并传递一个代表XML之旅一大块。

Answer 3

您需要指定您正在使用的数据库引擎。既然你已经指定了Linq标签，我假设你正在使用Microsoft SQL服务器。

您可以轻松地将文档作为字符串传递给存储过程。在此存储过程中，您可以定义一些临时表模式，并使用sp_xml_preparedocument，OPENXML（和相关函数）使用XML文档中的数据填充这些临时表。现在，您可以使用标准SQL从临时表中使用任何投影，连接或其他需要的转换插入到永久表中。内存在大多数情况下也不再是问题，因为它全部在MSSQL Server内完成。

更多信息： http://msdn.microsoft.com/en-us/library/aa260385(SQL.80).aspx http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx

例（未经测试）：

declare @docHandle int 
declare @xmlContent varchar(500) 
set @doc ='<root><items><item value="1"/><item value="2"/></item></root>' 

exec sp_xml_preparedocument @docHandle OUTPUT, @xmlContent 

insert into myTable ([ItemValue]) 
select value from OPENXML(@docHandle, '/root/items/item') 

exec sp_xml_removedocument @docHandle 

注意，这些是SQL 2000条的约定。在SQL 2005中，存在比这些存储过程前辈更具可读性的等效T-SQL约定。不过，我相信这些约定在SQL 2005和SQL 2008中仍然可用。