SSIS XML datasource - unbounded elements are NULL

Question

We're experiencing a problem while trying to import an XML file using SSIS. We have a more complex XML + XSD for which this problem occurs, but we were able to reproduce it on its own.

We have an XML file with several elements. We generated the XSD from it. Using the data visualizer we see that 4 rows are found, but the values are NULL.

What are we overlooking, or how can we solve this?

XML

<root>
  <index>a</index>
  <index>b</index>
  <index>n</index>
  <index>x</index>
</root>

XSD

<?xml version="1.0" encoding="Windows-1252"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="index" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Screenshot

Yes, I've seen this one: SSIS XMLSource only seeing null values in XML variable - but changing the source XML is not really an option for us.

Answer 1

We didn't solve this issue we created a workaround. Since we can't change what the source data looks like, we had to transform it ourselves. Therefore, we wrote an XSLT transform to transform the XML from:

<Tag>
  <Folder>Whatever</Folder>
  <Index>2014-03-31</Index>
  <Index>31454534</Index>
  <Index>3274</Index>
  <Index>Testname1 Testname2</Index>
</Tag>

To:

<Tags>
  <Tag Folder="Whatever" Index1="2014-03-31" Index2="31454534" Index3="3274" Index4="Testname1 Testname2" />
</Tags>

XSLT is below ( I got rid of all names and stuff, so this is not a working example ). We run the transform from the XML task ( as described here: http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx ). We save the result into a variable. Then we run a data-flow task with an XML source that loads the XML from the variable. Then all the rows DO appear. We then use this as input for our script-component where we have access to: Row.Folder, Row.Index1 ... etc.

XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:pmd="http://something"
                xmlns:default="http://something"
                xmlns:k="http://something"
                exclude-result-prefixes="msxsl"
>
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/">
    <k:TagList>
      <k:Tags>
        <xsl:apply-templates/>
      </k:Tags>
    </k:TagList>
  </xsl:template>

  <xsl:template match="default:Metadata">
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="pmd:Tag">
    <k:Tag>
      <xsl:attribute name="Folder">
        <xsl:value-of select="pmd:Folder" />
      </xsl:attribute>
      <xsl:attribute name="Index1">
        <xsl:value-of select="pmd:Index[1]" />
      </xsl:attribute>
      <xsl:attribute name="Index2">
        <xsl:value-of select="pmd:Index[2]" />
      </xsl:attribute>
      <xsl:attribute name="Index3">
        <xsl:value-of select="pmd:Index[3]" />
      </xsl:attribute>
      <xsl:attribute name="Index4">
        <xsl:value-of select="pmd:Index[4]" />
      </xsl:attribute>
    </k:Tag>
  </xsl:template>