Reading Data from XML Open Data Document

One Star

Reading Data from XML Open Data Document

Hi All,
I am trying to Parse XML based data from an excel based XML Open Document but I am not able to get the exact Result. The Column values are shifting from one place to another.Please refer to attached screen shot. My XML has the following format.
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet"
 xmlnsSmiley Surprised="urn:schemas-microsoft-comSmiley SurprisedfficeSmiley Surprisedffice"
 xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel"
 xmlns:ss="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet"
 xmlns:html=""
 xmlns:x2="">
 <DocumentProperties xmlns="urn:schemas-microsoft-comSmiley SurprisedfficeSmiley Surprisedffice">
  <Author>Jordan Kuhle</Author>
  <LastAuthor>Jordan Kuhle</LastAuthor>
  <Created>2014-11-19T17:56:16Z</Created>
  <Version>14.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-comSmiley SurprisedfficeSmiley Surprisedffice">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:excel">
  <WindowHeight>12075</WindowHeight>
  <WindowWidth>18195</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>30</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
  <FutureVer>11</FutureVer>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ssSmiley Frustratedize="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <NumberFormat ss:Format="@"/>
  </Style>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="Short Date"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="48" ss:ExpandedRowCount="1791" x:FullColumns="1"
   x:FullRows="1" ssSmiley Very HappyefaultRowHeight="15">
   <Column ss:Width="87"/>
   <Column ss:Width="109.5"/>
   <Column ss:Width="72"/>
   <Column ss:Width="56.25"/>
   <Column ss:Width="94.5"/>
   <Column ss:Width="97.5"/>
   <Column ss:Width="94.5"/>
   <Column ss:Width="109.5"/>
   <Column ss:Width="61.5"/>
   <Column ss:Width="86.25"/>
   <Column ss:Width="102.75"/>
   <Column ss:Width="97.5"/>
   <Column ss:Width="89.25"/>
   <Column ss:Width="64.5" ssSmiley Frustratedpan="1"/>
   <Column ss:Index="16" ss:Width="147.75"/>
   <Column ss:Width="100.5"/>
   <Column ss:Width="195.75"/>
   <Column ss:Width="112.5"/>
   <Column ss:Width="106.5"/>
   <Column ss:Width="76.5"/>
   <Column ss:Width="84"/>
   <Column ss:Width="62.25"/>
   <Column ss:Width="54"/>
   <Column ss:Width="291.75"/>
   <Column ss:Width="132"/>
   <Column ss:Width="108.75"/>
   <Column ss:Width="106.5"/>
   <Column ss:Width="97.5"/>
   <Column ss:Width="85.5"/>
   <Column ss:Width="90.75"/>
   <Column ss:Width="116.25"/>
   <Column ss:Width="92.25"/>
   <Column ss:Width="69"/>
   <Column ss:Width="90.75"/>
   <Column ss:Width="218.25"/>
   <Column ss:Width="121.5"/>
   <Column ss:Width="91.5"/>
   <Column ss:Width="148.5"/>
   <Column ss:Width="132.75"/>
   <Column ss:Width="95.25"/>
   <Column ss:Width="426"/>
   <Column ss:Width="114.75"/>
   <Column ss:Width="92.25"/>
   <Column ss:Width="249"/>
   <Column ss:Width="123"/>
   <Column ss:Width="101.25"/>
   <Column ss:Width="150.75"/>
   <Row>
    <Cell><Data ss:Type="String">dealerDivCode</Data></Cell>
    <Cell><Data ss:Type="String">dealerCountryCode</Data></Cell>
    <Cell><Data ss:Type="String">updateDate</Data></Cell>
    <Cell><Data ss:Type="String">dealerId</Data></Cell>
    <Cell><Data ss:Type="String">dealerTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">departmentCode</Data></Cell>
    <Cell><Data ss:Type="String">phoneTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">phoneCountryCode</Data></Cell>
    <Cell><Data ss:Type="String">areaCode</Data></Cell>
    <Cell><Data ss:Type="String">phoneNumber</Data></Cell>
    <Cell><Data ss:Type="String">fullPhoneNumber</Data></Cell>
    <Cell><Data ss:Type="String">dayOfWeekCode</Data></Cell>
    <Cell><Data ss:Type="String">closedIndicator</Data></Cell>
    <Cell><Data ss:Type="String">openTime</Data></Cell>
    <Cell><Data ss:Type="String">closeTime</Data></Cell>
    <Cell><Data ss:Type="String">addressMailabilityIndicator</Data></Cell>
    <Cell><Data ss:Type="String">addressTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">addressLine1</Data></Cell>
    <Cell><Data ss:Type="String">cityName</Data></Cell>
    <Cell><Data ss:Type="String">stateProvinceCode</Data></Cell>
    <Cell><Data ss:Type="String">countryCode</Data></Cell>
    <Cell><Data ss:Type="String">zipPostalCode</Data></Cell>
    <Cell><Data ss:Type="String">longitude</Data></Cell>
    <Cell><Data ss:Type="String">latitude</Data></Cell>
    <Cell><Data ss:Type="String">addressLine2</Data></Cell>
    <Cell><Data ss:Type="String">categorizationTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">categorizationCode</Data></Cell>
    <Cell><Data ss:Type="String">indicatorTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">serviceTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">dateTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">dealerDateData</Data></Cell>
    <Cell><Data ss:Type="String">dealerLanguageCode</Data></Cell>
    <Cell><Data ss:Type="String">statusTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">statusCode</Data></Cell>
    <Cell><Data ss:Type="String">nameTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">dealerNameData</Data></Cell>
    <Cell><Data ss:Type="String">relationshipTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">relatedDealerId</Data></Cell>
    <Cell><Data ss:Type="String">dealerPreferenceTypeCode</Data></Cell>
    <Cell><Data ss:Type="String">dealerPreferencetDetail</Data></Cell>
    <Cell><Data ss:Type="String">urlFunctionCode</Data></Cell>
    <Cell><Data ss:Type="String">url</Data></Cell>
    <Cell><Data ss:Type="String">typeCodeCategory</Data></Cell>
    <Cell><Data ss:Type="String">typeCodeValue</Data></Cell>
    <Cell><Data ss:Type="String">typeCodeDesc</Data></Cell>
    <Cell><Data ss:Type="String">parentTypeCodeValue</Data></Cell>
    <Cell><Data ss:Type="String">numberOfDealers</Data></Cell>
    <Cell><Data ss:Type="String">exportTimeStamp</Data></Cell>
   </Row>
   <Row>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">VW</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">USA</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s63"><Data ss:Type="DateTime">2014-10-09T00:00:00.000</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">401010</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">1</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">CORP</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">BUSFX</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">617</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">4895001</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">6174895001</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ss:Index="25" ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s63"/>
    <Cell ssSmiley FrustratedtyleID="s62"><Data ss:Type="String">E</Data></Cell>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ss:Index="39" ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ssSmiley FrustratedtyleID="s62"/>
    <Cell ss:Index="48" ssSmiley FrustratedtyleID="s62"/>
   </Row>
Seventeen Stars

Re: Reading Data from XML Open Data Document

Why do you try this hard way. There are excel related components doing all this work for you.

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download