CODEWITHSUNDEEP
xmlxsltssisxsdvisual-studio-2015
danny taki
danny taki

Reputation: 450

XML Mixed content model with complex types SSIS error

I have an XML file for a government document and a XSD file that it references. I'm trying to extract information from the XML (chemical name, UN, and hazard classification) and store it into a table in SQL Server 2014. However, whenever I perform an ETL with an XML source, using SSIS, I get the error: The XML Source Adapter does not support mixed content model on Complex Types. How can I work around this? Some ideas I thought about and tried to understand better were: using an XSLT transform on the XML to only retain the pertinent information or use C# and XPath to select only the nodes I want. But this has turned into something a bit harder than I originally thought. Any help or direction is appreciated.

http://www.gpo.gov/fdsys/bulkdata/CFR/2014/title-49/CFR-2014-title49-vol2.xml http://www.gpo.gov/fdsys/bulkdata/CFR/resources/CFRMergedXML.xsd

Upvotes: 0

Views: 2742

Answers (1)

jdweng
jdweng

Reputation: 34421

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\CFR-2014-title49-vol2.xml";
        static void Main(string[] args)
        {
            XDocument doc = XDocument.Load(FILENAME);
            XElement chapter = doc.Descendants("GPOTABLE").Where(x => x.Element("TTITLE") != null && x.Element("TTITLE").Value == "§ 172.101 Hazardous Materials Table").FirstOrDefault();

            var results = chapter.Elements().Where(x => x.Name == "ROW" && x.Attribute("RUL") == null && (x.Elements("ENT").Count() == 14 || x.Elements("ENT").Count() == 2)).Select(y => new
            {
                chemical_name = y.Elements("ENT").Count() == 14 ? y.Elements("ENT").Skip(1).FirstOrDefault().Value
                   : y.Elements("ENT").Skip(1).FirstOrDefault().Value == ""  
                      ? string.Join("", y.Elements("ENT").Skip(1).FirstOrDefault().Descendants().Select(z => z.NextNode == null ? z.Value : z.Value + z.NextNode.ToString()).ToArray()) 
                      : string.Join("", y.Elements("ENT").Skip(1).FirstOrDefault().Value),
                classification = y.Elements("ENT").Count() == 14 ? y.Elements("ENT").Skip(2).FirstOrDefault().Value : null,
                UN = y.Elements("ENT").Count() == 14 ? y.Elements("ENT").Skip(3).FirstOrDefault().Value : null
            }).Where(a => a.chemical_name.Length > 0).ToList();
        }
    }
}
​

Upvotes: 1

Related Questions