SSIS: XML not being inserted into table

Question

In SSIS I have a generated XML file based on a webservice. I need to read the file to generate a dataset.
The problem that I have is that the "XML Source" is not reading the file correctly. I'm not getting any data. This fact is based on a Data Viewer that I have defined.

There is an inline schema defined in the XML, so I don't need to define a location for the XSD location.
When I click Columns in the "XML Source Editor" I see the different columns that are available.

XML Source:

<?xml version="1.0" encoding="utf-16"?>
<DataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Table1">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="companyid" type="xs:decimal" minOccurs="0" />
                <xs:element name="companyname" type="xs:string" minOccurs="0" />
                <xs:element name="straatnaam" type="xs:string" minOccurs="0" />
                <xs:element name="huisnummer" type="xs:string" minOccurs="0" />
                <xs:element name="postcode" type="xs:string" minOccurs="0" />
                <xs:element name="plaatsnaam" type="xs:string" minOccurs="0" />
                <xs:element name="provincie" type="xs:string" minOccurs="0" />
                <xs:element name="land" type="xs:string" minOccurs="0" />
                <xs:element name="hrovernameorgrol" type="xs:unsignedByte" minOccurs="0" />
                <xs:element name="supporttelefoon" type="xs:string" minOccurs="0" />
                <xs:element name="supportemail" type="xs:string" minOccurs="0" />
                <xs:element name="faxnummer" type="xs:string" minOccurs="0" />
                <xs:element name="lastupdate" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet>
      <Table1 diffgr:id="Table11" msdata:rowOrder="0">
        <companyid>1</companyid>
        <companyname>K8B</companyname>
        <straatnaam>Mod</straatnaam>
        <huisnummer>1</huisnummer>
        <postcode>1111 AA</postcode>
        <plaatsnaam>Aretrea</plaatsnaam>
        <provincie>AB</provincie>
        <land>H</land>
        <hrovernameorgrol>1</hrovernameorgrol>
        <supporttelefoon>011011111</supporttelefoon>
        <supportemail>[e-mail]</supportemail>
        <faxnummer />
        <lastupdate>19/08/2010</lastupdate>
      </Table1>
    </NewDataSet>
  </diffgr:diffgram>
</DataSet>

Any ideas on how to get the XML Source to read the XML file so that I can generate a dataset(object) out of this XML?

Input much appreciated!!

Answer 1

You will need to de-couple the schema from the XML portion to process it.

---

Arthur My Blog

Answer 2

you can read the data form web service, store in a variable. then using script task, replace all the lines containig xs:. You have the xml file data. Then in another variable you replace actual variable with these contents, now store it in another variable : you have the schema. Store that file in a location, you can use variable for that also. You can use that location as the path for XSD. and pass the first variable as the data.(I guess the web service gives you the data + xsd together).

regards

joon

Answer 3

you can read the data form web service, store in a variable. then using script task, replace all the lines containig xs:. You have the xml file data. Then in another variable you replace actual variable with these contents, now store it in another variable : you have the schema. Store that file in a location, you can use variable for that also. You can use that location as the path for XSD. and pass the first variable as the data.(I guess the web service gives you the data + xsd together).

regards

joon

using Xslt would be a much better approach to transform the XML the way its required. 

---

Please mark the post as answered if it answers your question