sql - 将巨大的复杂 XML 解析/切碎到 SQL Server 2008 数据库(30 多个表)

Question

我已经读过:The Best Way to shred XML data into SQL Server database columns和 Looking for a good Bulk Insert XML Shredding example for SQL 2005 .

我发帖的不同之处在于我使用的是 BizTalk 2009 和 SQL 2008。

我从使用 BizTalk 的供应商那里收到了一个巨大的 XML 结构。客户已将 XML 结构规范化为 MS/SQL Server 2008 数据库上的大约 30 个表。

有什么神奇的解决办法吗？

在我看来，这些是选项:

1) BizTalk SQL 适配器仅适用于简单的平面数据库(没有很多连接和一对多关系)。

2) 编写WCF程序 a) 使用 LINQ 并公开 LINQ 对象 b) 使用传统的XML DOM 或SAX 解析并构建ADO.NET 存储在数据库中

3) 编写一个使用 Open/XML 的复杂存储过程。

4) 将数据库临时存储在 SQL/XML 列中，然后使用其他工具“分解和规范化”数据。 SSIS 中有什么可以做到这一点吗？

5) 将数据保留在 XML 列中，并使用 XML 索引并且从不对其进行规范化。在 View 中嵌入难看的 XQuery/Xpath 语句。不确定响应时间或查询是否足够。生成 xqueries 和 View 所花的时间可能与执行上述其他步骤之一所花的时间一样长。

我猜测 #2 或 #3 每张 table 至少需要一到两个小时，因此如果我们有 30 张 table ，至少需要 30(如果不是 60 小时)各种繁琐且容易出错的工作。

谢谢，

尼尔·沃尔特斯

更新 12/23:一些示例数据:

 <ns0:ValAgg xmlns:va="http://msbinfo.com/expresslync/rct/valuation" xmlns:ns0="http://TFBIC.RCT.BizTalk.Orchestrations.ValAgg">
- <MainStreetValuation xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://msbinfo.com/expresslync/rct/valuation">
<ValuationIdentifier>
  <RecordId>1928876</RecordId> 
  <PolicyNumber>ESTIMATE-1928876</PolicyNumber> 
  <VersionId>6773220</VersionId> 
  </ValuationIdentifier>
  <RecordType>EST</RecordType> 
  <PolicyStatus>Complete</PolicyStatus> 
  <DataSource>WEB</DataSource> 
   <bunch more here/> 
<valuationAggregateFlat xmlns="http://tempuri.org/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <policyNumber>ESTIMATE-1928876</policyNumber> 
  <recordId>1928876</recordId> 
  <versionId>6773220</versionId> 
  <updateTimeStamp>2009-12-14T14:50:30.743</updateTimeStamp> 
  <replacementCost>166129</replacementCost> 
  <yearBuilt>1999</yearBuilt> 
  <totalLivingAreaSqFt>2000</totalLivingAreaSqFt> 
  <primaryRCTRoofTypeCode>15012</primaryRCTRoofTypeCode> 
  <TOPSRoofType>COPR</TOPSRoofType> 
  <StdFireRoofType>COPR</StdFireRoofType> 
  <primaryRTCConstructionTypeCode>10016</primaryRTCConstructionTypeCode> 
  <constructionType>BV</constructionType> 
  <hailProofIndicator>false</hailProofIndicator> 
  <anyWoodRoofIndicator>false</anyWoodRoofIndicator> 
  <allMetalRoofIndicator>true</allMetalRoofIndicator> 
  </valuationAggregateFlat>
</ns0:ValAgg>

您看到“MainStreetValuation”的地方也可能是其他几个复杂类型，例如“HighValueValuation”，其中整个结构对于拥有精美元素的房屋来说是不同的。

Answer 1

快速说明:您使用 BizTalk 2009 本身并不会阻止您也使用 SSIS 之类的工具来分解和处理 XML。

---

下面的评论太长了:

XML 源代码有一个问题需要注意。考虑如下 XML 结构:

<root>
    <parent attr1="value1" attr2="value2">
        <child attrc1="valuec1" attrc2="valuec2"/>
        <child attrc1="valuec1" attrc2="valuec2"/>
    </parent>
    <parent> ... </parent>
    ...
</root>

通过 XML 源处理的结果将是两个输出:一个带有 attr1 和 attr2，另一个带有 attrc1 和 attrc2。输出都是相互异步处理的。您需要通过 SSIS 将引入的人工列来关联父行和子行。每个 parent 都有一个 id 列，每个 child 都有相同的 id 值作为“外键”。您可能需要在数据库中做一些工作来匹配两者。