Разбор / уничтожение огромного сложного XML в базу данных SQL Server 2008 (более 30 таблиц)

Question

Я уже читал это: Лучший способ измельчить XML-данные в столбцы базы данных SQL Server и Ищу хороший пример массовой вставки XML-измельчения для SQL 2005.

Разница в том, почему я публикую сообщения, заключается в том, что я использую BizTalk 2009 и SQL 2008.

Я получаю огромную структуру XML от поставщика, использующего BizTalk. Клиент нормализовал структуру XML примерно до 30 таблиц в базе данных MS/SQL Server 2008.

Есть ли какое-нибудь волшебное решение?

Похоже, мне эти варианты:

1) Адаптер BizTalk SQL подходит только для простых плоских баз данных (не так много объединений и отношений "один ко многим").

2) Напишите программу WCF а) используйте LINQ и предоставьте объект LINQ б) используйте традиционный синтаксический анализ XML DOM или SAX и соберите ADO.NET для хранения в базе данных

3) Написать сложный сохраненный Proc, который использует Open / XML.

4) Временно сохраните базу данных в столбце SQL/XML, затем используйте какой-либо другой инструмент для "измельчения и нормализации" данных. Есть ли в SSIS что-нибудь, что могло бы сделать это?

5) Оставьте данные в столбце XML, используйте индексы XML и никогда не нормализуйте их. Внедрить уродливые операторы XQuery/Xpath в представление. Не уверен, что время ответа или запросов будет достаточно. Для генерации xqueries и представлений может потребоваться столько же времени, сколько и для выполнения других шагов, описанных выше.

Я предполагаю, что для № 2 или № 3 потребуется по крайней мере один или два часа на стол, поэтому, если у нас есть 30 таблиц, по крайней мере, 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 Source. Рассмотрим структуру 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. У каждого родителя будет столбец идентификатора, а у каждого потомка будет то же значение идентификатора, что и у "внешнего ключа". Возможно, вам придется немного поработать в вашей базе данных, чтобы соответствовать двум.