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

Mudassar A 486 Reputation points
2021-03-18T15:25:18.603+00:00

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 .

79294-image.png

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,731 Reputation points
    2021-03-18T15:45:46.973+00:00

    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>  
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2021-03-19T09:23:19.347+00:00

    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.