SSIS: XML not being inserted into table

• Henk Geurs

  SSC Veteran

  Points: 295

  More actions

  August 17, 2012 at 8:06 am

  #151046

  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>oot@oot.com</supportemail>

  <faxnummer />

  <lastupdate>19/08/2010</lastupdate>

  </Table1>

  </NewDataSet>

  </diffgr:diffgram>

  </DataSet>

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

  Input much appreciated!!