SSIS XMLSource only seeing null values in XML vari

Question

I have a Data Flow task with an XMLSource that references an XML Variable. The DataFlow task does recognize that there are x number of rows in the variable, but it only sees null values in every row:

The xml variable value:

<?xml version="1.0" encoding="utf-8"?>
<words>
   <word>butter</word>
   <word>crispy</word>
</words>

I used this source to generate the XSD within the XMLSource Editor - here is the auto-generated XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="words">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="word" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

The package compiles, executes, and processes all the rows in my XML, but only sees nulls rather than the actual text strings... Here is a shot of the DataViewer displaying 2 rows after reading the XML variable:

Answer 1

I discovered a way to get the values to populate... I'll post it here without giving myself points, in case anyone else encounters the same problem. This is just a "how to fix", but I'll give credit to anyone who can explain the "deeper whys".

Essentially, the XML needed to be wrapped in another root node:

<?xml version="1.0" encoding="utf-8"?>
<datarows>
   <words>
      <word>bacon</word>
      <word>roasted</word>
      <word>pork</word>
      <word>edamame</word>
   </words>
</datarows>

Even though the original XML I used was valid, SSIS wanted it to be wrapped in an additional root node, which I named datarows. Once I did that the package recognized the word values and completed successfully.

The associated schema:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="datarows">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="1" name="words">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="word" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Answer 2

I too had the same problem,. I was trying to consume a web service and import the output xml into a table in sql 2008.

The issue is really with the namespace that gets generated in the output xml by the webservice. The trick I have used was
1. Stored the output of the web service in a package level variable
2. Add a 'script task', to replace the unwanted name space.
3. Then used 'XMl Source Task' to import the data into a table.

-Kris...