Using a SSIS XML Source store relative xml elements

Question

Using a SSIS XML Source I would like to read the following XML

<Stock>
  <OnHand>1</OnHand>
  <Proximity>xx</Proximity>
  <Reserved>2</Reserved>
  <Proximity>yy</Proximity>
  <OnOrder>3</OnOrder>
  <Proximity>zz</Proximity>
  <Cbo>4</Cbo>
  <Proximity>zz</Proximity>
</Stock>

The XML cannot be changed because it is an international standard called Onix.

As you can see there is a Proximity after each row.

I would somehow like to store them in the right order, or call the Proximity after OnHand for OnHandProximity.

The schema that follows this XML looks like this:

<xs:sequence>
  <xs:element ref="OnHand" />
  <xs:element minOccurs="0" ref="Proximity" />
  <xs:sequence minOccurs="0">
    <xs:element ref="Reserved" />
    <xs:element minOccurs="0" ref="Proximity" />
  </xs:sequence>
  <xs:sequence minOccurs="0">
    <xs:element ref="OnOrder" />
    <xs:element minOccurs="0" ref="Proximity" />
  </xs:sequence>
  <xs:sequence minOccurs="0">
    <xs:element ref="CBO" />
    <xs:element minOccurs="0" ref="Proximity" />
  </xs:sequence>
</xs:sequence>

Unfortunately this correctly gives this error in SSIS XML Source:

The XML Source was unable to process the XML data. Ambiguous complexType definition. The element "stock" has multiple members named "Proximity".

And my question is of course if anyone have suggestions for handling XML like this without loosing the information that the relative position is.

There also is detail that the lines are not mandatory, but I think a first solution can ignore that detail.

Answer 1

A work-around could be transforming the XML before importing. Consider do something like the following.

As I am not the best in stylesheet transformation - feel free to comment or provide better solutions.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">

    <xsl:output method="xml" indent="yes"/>

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

    <xsl:template match="Stock/OnHand">
        <xsl:element name="./OnHand">
            <xsl:element name="OnHandValue">
                <xsl:value-of select="current()"/>
            </xsl:element>
            <xsl:element name="OnHandProximity">
                <xsl:choose>
                    <xsl:when test="following-sibling::*[1][self::Proximity]" >
                        <xsl:value-of select="following-sibling::*[1]" />
                    </xsl:when>
                </xsl:choose>
            </xsl:element>
        </xsl:element>
    </xsl:template> 
    <xsl:template match="Stock/Reserved">
        <xsl:element name="Reserved">
            <xsl:element name="ReservedValue">
                <xsl:value-of select="current()"/>
            </xsl:element>
            <xsl:element name="ReservedProximity">
                <xsl:choose>
                    <xsl:when test="following-sibling::*[1][self::Proximity]" >
                        <xsl:value-of select="following-sibling::*[1]" />
                    </xsl:when>
                </xsl:choose>
            </xsl:element>
        </xsl:element>
    </xsl:template> 
    <xsl:template match="Stock/OnOrder">
        <xsl:element name="OnOrder">
            <xsl:element name="OnOrderValue">
                <xsl:value-of select="current()"/>
            </xsl:element>
            <xsl:element name="OnOrderProximity">
                <xsl:choose>
                    <xsl:when test="following-sibling::*[1][self::Proximity]" >
                        <xsl:value-of select="following-sibling::*[1]" />
                    </xsl:when>
                </xsl:choose>
            </xsl:element>
        </xsl:element>
    </xsl:template> 
    <xsl:template match="Stock/CBO">
        <xsl:element name="CBO">
            <xsl:element name="CBOValue">
                <xsl:value-of select="current()"/>
            </xsl:element>
            <xsl:element name="CBOProximity">
                <xsl:choose>
                    <xsl:when test="following-sibling::*[1][self::Proximity]" >
                        <xsl:value-of select="following-sibling::*[1]" />
                    </xsl:when>
                </xsl:choose>
            </xsl:element>
        </xsl:element>
    </xsl:template> 

    <xsl:template match="Stock/Proximity" />
</xsl:stylesheet>   

Answer 2

Use Script Component as Source

XML Source doesn't support these types of XML files, you should add a Script Component as Source, Use a C# script to deserialize the XML file using System.Xml namespace or similar assemblies and Generate the Output Rows. You can pass the XML file path using Variables to the Script.

Helpful links

Similar Issue

• Loading ONIX XML to database

Check this link, it may contains some workaround

Using Script Component as Source

• SSIS Script Component as Source
• Creating a Source with the Script Component
• Script Component as Source – SSIS

Handling ONIX Xml in .Net

• ONIX-Data project
• ONIXNOW project
• Validating xml against the Onix 2.1 dtd with dotNet
• Deserializing relative path XML