XML Adapter Does Not Support Mixed Content on Complex Types VS2019 SSIS

Question

I have a XML datamodel which I m trying to load in a table.

Goal is to flatten the xml in a table

The XML is SAP Successfactors Succession data model and when I analyze it in Power BI it has multiple sections as shown below in the pic .

In SSIS , first I overcame namespaces by following the article-
http://surendrathotamsbiproffesional.blogspot.com/2014/01/error-unable-to-infer-xsd-from-xml-file.html
but now I m getting XML Adapter Does Not Support Mixed Content on Complex Types.
Below is a section from XML and this pattern i see around 421 places in the XML

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">  
  <xs:element name="succession-data-model">  
    <xs:complexType>  
      <xs:sequence>  
        <xs:element minOccurs="0" name="description" type="xs:string" />  
        <xs:element minOccurs="0" maxOccurs="unbounded" name="standard-element">  
          <xs:complexType mixed="true">  
            <xs:sequence minOccurs="0">  
              <xs:element minOccurs="0" maxOccurs="unbounded" name="label">  
                <xs:complexType>  
                  <xs:simpleContent>  
                    <xs:extension base="xs:string">  
                      <xs:attribute name="lang" type="xs:string" use="optional" />  
                    </xs:extension>  
                  </xs:simpleContent>  
                </xs:complexType>  
              </xs:element>  
              <xs:element minOccurs="0" name="picklist">  
                <xs:complexType>  
                  <xs:attribute name="id" type="xs:string" use="optional" />  
                </xs:complexType>  
              </xs:element>  

How can I overcome this problem of parsing ?What is the recommended way of doing this?

Expected o/p :

Separate tables

1. standard-element table containing only standard-element data
2. userinfo-element table containing only userinfo-element data
3. background -element table containing only background -element data
4. tab-element table containing only tab-element data
5. hris-element table containing only hris-element data
6. hris-action table containing only hris-action data
7. element-permission table containing only element-permission data
8. view-template table containing only view-template data

Input xml:

79501-new-compressed-xml.xml

Answer 1

Hi @Anonymous ,

Yes, you bumped into the SSIS XML Source Adapter limitation.

A solution for it is very simple. You need to use SSIS XML Task, operation XSLT transformation to re-shape input XML into a digestable XML format. After that you will be able to shred that XML and load it into a target database.

Just extend the XSLT that you already supposedly using.

UPDATE
Based on your input XML, it contains the following elements under the root.

distinct-values(/succession-data-model/*/local-name(.))  

- description

• standard-element
• userinfo-element
• background-element
• tab-element
• hris-element
• hris-action
• custom-filters
• dg-filters
• view-template
• hris-sync-mappings

Let's say you need XML with the standard-element elements only.
Here is the XSLT that will create it. The XSLT is using so called Identity Transform pattern.

The last template in the XSLT disables output of enlisted XML elements. For example, you want to add the userinfo-element element to the output XML. So, just delete it from the last template. As end result, output XML wil contain both standard-element as well as userinfo-element elements. It gives you tremendous flexibility.

It should give you a good head start.

XSLT

<?xml version='1.0'?>  
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">  
   <xsl:output method="xml" encoding="utf-8" indent="yes" omit-xml-declaration="yes"/>  
   <xsl:strip-space elements="*"/>  

   <xsl:template match="node() | @*">  
      <xsl:copy>  
         <xsl:apply-templates select="node() | @*"/>  
      </xsl:copy>  
   </xsl:template>  

   <xsl:template match="description | userinfo-element | background-element | tab-element | hris-element | hris-action | custom-filters | dg-filters | view-template | hris-sync-mappings">  
   </xsl:template>  
</xsl:stylesheet>  

Answer 2

Hi @Anonymous ,

May I know if you run the XML Task correctly ?

The XML task is used to work with XML data. Using this task, a package can retrieve XML documents, apply operations to the documents by using Extensible Stylesheet Language Transformations (XSLT) style sheets and XPath expressions, merge multiple documents, or validate, compare, and save the updated documents to files and variables.

Please refer to XML Task and An Overview of the XML Task in SSIS Packages.

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.