question

DataArtist avatar image
0 Votes"
DataArtist asked Monalv-msft edited

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

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
image.png (25.6 KiB)
image.png (13.4 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DataArtist,

There is no need to use Answers for questions. You can delete them.
Just use the original question for that via Edit.

To help you with re-shaping via XSLT, I need the following:

  • Input XML

  • desired output XML

0 Votes 0 ·
DataArtist avatar image DataArtist YitzhakKhabinsky-0887 ·

Input xml attached


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 is in the top post

0 Votes 0 ·

@DataArtist,

I updated the answer. Please check its UPDATE section.

0 Votes 0 ·
DataArtist avatar image DataArtist YitzhakKhabinsky-0887 ·

Thanks , I m going to work on this

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @DataArtist,

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>
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @DataArtist ,

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Need help in cost , shaping XML to digest the data .

The input XML is attached in the post and expected output is specified

I have tried to use XML tasks and it fails as it requires XML shaping

0 Votes 0 ·