SQL Server XML Parsing question

Question

I'm trying to use SQL Server Integration Services (SSIS) to parse an XML file and map the elements to database columns across several tables in a single database. However, when I use the Data Flow Task->XML Source to try and parse an example XML file (that file is located here , XSD is located here ), it says

 "http://www.exchangenetwork.net/schema/TRI/4:TransferWasteQuantity" has multiple members named "http://www.exchangenetwork.net/schema/TRI/F:WasteQuantityCatastrophicMeasure"

 Is there any way to get SSIS to parse XML data such as this? This schema changes regularly so I'd prefer to do as little parsing code outside of the data mappings as possible. Also if there's a better way to do this outside of SSIS (say, by using SQL Server Analysis Services) then that would work too.

Answer 1

I tested the given .xml & .xsd files as data flow source in SSIS 2008, and I got multiple namespaces error.

Some related links:

Problem validating in SSIS XML Task

Multiple namespaces in an XML document


Let us know if helpful.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 2

Thanks for the reply.  I saw both of these answers online but I'm wondering if the namespace is the issue (its TRI throughout the entire XML document) I think the problem is specifically that there's one element that is not unique throughout the document.  Unfortunately, due to the nature of the data, all elements must be processed into the database so that's why I'm currently attempting to overcome.