Creating XSLT for Xml file to output an xml file

[email protected] · March 14th, 2014, 12:12 PM

The Input XML is

Code:

 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Warwick Boulter</Author>
  <LastAuthor>Warwick Boulter</LastAuthor>
  <Created>2014-03-05T01:10:56Z</Created>
  <LastSaved>2014-03-13T20:39:00Z</LastSaved>
  <Company>Six Foot Sultans</Company>
  <Version>12.0</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>15260</WindowHeight>
  <WindowWidth>21600</WindowWidth>
  <WindowTopX>-20</WindowTopX>
  <WindowTopY>-20</WindowTopY>
  <Date1904/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Verdana"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s16">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
   </Borders>
   <Font ss:Color="#000000" ss:Bold="1"/>
   <Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s17">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
   </Borders>
   <Font ss:Color="#000000"/>
   <Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s18">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"
     ss:Color="#C0C0C0"/>
   </Borders>
   <Font ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s19">
   <Font/>
  </Style>
  <Style ss:ID="s20">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:Bold="1"/>
  </Style>
  <Style ss:ID="s21">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font/>
  </Style>
  <Style ss:ID="s22">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font/>
   <NumberFormat ss:Format="Short Date"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="48" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s19" ss:DefaultRowHeight="15.0">
   <Column ss:StyleID="s19" ss:AutoFitWidth="0" ss:Width="195.0"/>
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="153.0"/>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s16"><Data ss:Type="String">DBSchema field</Data></Cell>
    <Cell ss:StyleID="s20"><Data ss:Type="String">Example data entry</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s17"><Data ss:Type="String">Director</Data></Cell>
    <Cell><Data ss:Type="String">Patrick Filetti</Data></Cell>
   </Row>

---------------

Output XML should be

<Record>
<Main>
<Director>Patrick Filetti</Director>
<Upload Date>2014-03-05T00:00:00.000</Upload Date>
------------------------------
</Record>
</Main>

The XSLT I have developed is not working:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="by-Data" match="Data" use="." />

<xsl:template match="/Workbook/Worksheet/Table">
<Record>
<xsl:for-each select="/Data[generate-id(.) = generate-id(key('by-Data', .)[1])]">
<xsl:element name="{Data}" />
<xsl:value-of select="." />
</xsl:for-each>
</Record>
</xsl:template>

</xsl:stylesheet>

Please let me know where I am Incorrect and how to do it.