Parsing / Destroying A huge set of SQL Server 2008 XML databases (over 30 tables)

Question

I already read this: The best way to crop XML data in the columns of a SQL Server database and Look for a good mass Insert XML Shredding example for SQL 2005 .

The differences in why I publish is that I use BizTalk 2009 and SQL 2008.

I get a huge XML structure from a provider using BizTalk. The client normalized the XML structure to approximately 30 tables in the MS / SQL Server 2008 database.

Is there a magic solution?

It seems to me that these are options:

1) The BizTalk SQL adapter is only suitable for simple flat databases (there are not many one-to-many relationships).

2) Write a WCF program a) use LINQ and expose a LINQ object b) use traditional XML DOM or SAX parsing and build ADO.NET for storage in the database

3) Write a Stored Proc package that uses Open / XML.

4) Store the database temporarily in the SQL / XML column, and then use another tool to “shred and normalize” the data. Is there anything in SSIS to do this?

5) Leave the data in an XML column and use XML indexes and never normalize it. Insert the ugly XQuery / Xpath statements into the view. Not sure if response time or inquiries will be adequate. It may take so long to generate xqueries and views, as one of the other steps above would do.

, # 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-. 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. id, id, " ". , , .