I read this already: http://stackoverflow.com/questions/61233/the-best-way-to-shred-xml-data-into-sql-server-database-columns and http://stackoverflow.com/questions/223376/looking-for-a-good-bulk-insert-xml-shredding-example-for-sql-2005.
The differences of why I'm posting is that I'm using BizTalk 2009 and SQL 2008.
I'm receiving a huge XML structure from a vendor using BizTalk. The client has normalized the XML structure into about 30 tables on a MS/SQL Server 2008 database.
Is there any magic solution yet?
Seems like to me these are the options:
1) BizTalk SQL adapter only good for simple flat databases (not a lot of joins and one-to-many relationships).
2) Write a WCF program a) use LINQ and expose the LINQ object b) use traditional XML DOM or SAX parsing and build ADO.NET to store in database
3) Write a complex Stored Proc that uses Open/XML.
4) Store the database temporarily in an SQL/XML Column, then use some other tool to "shred and normalize" the data. Is there anything in SSIS that would do this?
5) Leave the data in an XML column, and use XML indices and never normalize it. Embed the ugly XQuery/Xpath statements in a view. Not sure if response time or queries would be adequate. Might take as long to generate the xqueries and views as it would to do one of the other steps above.
I'm guessing that #2 or #3 would take at least one or two hours per table, thus if we have 30 tables, at least 30 (if not 60 hours) of various tedious boring and error-prone work.
Thanks,
Neal Walters
Update 12/23: Some sample data:
<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>
Where you see "MainStreetValuation" could also be a couple of other complex types, such as "HighValueValuation" where the entire structure is different for homes that have fancy stuff.