Looping through XML

[sandeepm]

Hello Experts,

I have got a following input file :

Code: Select all

<Worksheet ss:Name="Sheet1">

  <Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="11" x:FullColumns="1"

   x:FullRows="1" ss:DefaultRowHeight="15">   

   <Row>    

    <Cell><Data ss:Type="String">Columns</Data></Cell>

    <Cell><Data ss:Type="String">Width </Data></Cell>

    <Cell><Data ss:Type="String">Color </Data></Cell>

    <Cell><Data ss:Type="String">Comments </Data></Cell>    

    <Cell><Data ss:Type="String">Date</Data></Cell>

    <Cell><Data ss:Type="String">Status </Data></Cell>    

   </Row>

   <Row>    

    <Cell><Data ss:Type="String">Column 1</Data></Cell>

    <Cell><Data ss:Type="String">Width 1</Data></Cell>

    <Cell><Data ss:Type="String">Color 1</Data></Cell>

    <Cell><Data ss:Type="String">Comments 1</Data></Cell>    

    <Cell><Data ss:Type="String">Date 1</Data></Cell>

    <Cell><Data ss:Type="String">Status 1</Data></Cell>    

   </Row>

   <Row>    

    <Cell><Data ss:Type="String">Column 2</Data></Cell>

    <Cell><Data ss:Type="String">Width 2</Data></Cell>

    <Cell><Data ss:Type="String">Color 2</Data></Cell>

    <Cell><Data ss:Type="String">Comments 2</Data></Cell>    

    <Cell><Data ss:Type="String">Date 2</Data></Cell>

    <Cell><Data ss:Type="String">Status 2</Data></Cell>    

   </Row>   

 </Table>

</worksheet>

And need to have a following output:

Code: Select all

<test>



<Columns>Column 1</Columns>

<Width>Width 1</Width>

<Color>Color 1</Color>

<Comments>Comments 1</Comments>

<Date>Date 1</Date>

<Status>Status 1</Status>



<Columns>Column 2</Columns>

<Width>Width 2</Width>

<Color>Color 2</Color>

<Comments>Comments 2</Comments>

<Date>Date 2</Date>

<Status>Status 2</Status>



</test>

Please can you suggest how to achieve this efficiently.

Big Thanks in advance.

Cheers, Sandeep

[sandeepm]

Hey All,

I got the solution to my problem. Here is the xsl:

Code: Select all

<?xml version='1.0'?>



<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

  exclude-result-prefixes="ss"

  version="2.0">

  

<xsl:output indent="yes" omit-xml-declaration="no" method="xml"/>



<xsl:template match="/">

  



<xsl:for-each select="/ss:Workbook/ss:Worksheet[1]/ss:Table[1]/ss:Row[position() > 1]">

    <Columns><xsl:value-of select="ss:Cell[1]/ss:Data[1]/text()"/></Columns>

    <Width><xsl:value-of select="ss:Cell[2]/ss:Data[1]/text()"/></Width>

    <Color><xsl:value-of select="ss:Cell[3]/ss:Data[1]/text()"/></Color>

    <Comments><xsl:value-of select="ss:Cell[4]/ss:Data[1]/text()"/></Comments>

    <Date><xsl:value-of select="ss:Cell[5]/ss:Data[1]/text()"/></Date>

    <Status><xsl:value-of select="ss:Cell[6]/ss:Data[1]/text()"/></Status>

    <xsl:text>&#xa;</xsl:text> <!-- for newline character -->    

</xsl:for-each>



  

</xsl:template>

  

</xsl:stylesheet>

Cheers, Sandeep