How to transform XML with attributes?

Question

I'm trying to transform this XML into an HTML table using XSLT.

I've posted the XSLT below. But this XSLT won't output anything for me except for plain text, what I need is an HTML table, though.

Any ideas would be appreciated.

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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">

 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>7725</WindowHeight>
  <WindowWidth>17790</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>0</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Worksheet ss:Name="Page1">
  <Table ss:ExpandedColumnCount="22" ss:ExpandedRowCount="11465" x:FullColumns="1"
   x:FullRows="1">
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s62"><Data ss:Type="String">TERM CD</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">TERM LD</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">CAMPUS CD</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">SESSION CD</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">SESSION SD</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">SESSION LD</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">ACAD CAR CD</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">ACAD GRP CD</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>5</ActiveRow>
     <ActiveCol>5</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

I've come up with xslt below but it doesn't seem to work. Any ideas?

<?xml version="1.0" encoding="UTF-8" ?> 
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
 <xsl:template match="/Workbook/Worksheet/Table"> 
 <html> 
 <body> 
<h2>University of Colorado Boulder</h2> 
<table border="1"> 

    <xsl:for-each select="Row"> 
    <tr> 
        <xsl:for-each select="Cell/Data"> 
        <td> 
            <xsl:value-of select="text()" /> 
        </td> 
        </xsl:for-each> 
    </tr> 
    </xsl:for-each> 
</table> 
</body> 
</html> 
</xsl:template> 
</xsl:stylesheet>

Your input XML is in a namespace so you need to take it into account in your XSLT. — Sebastien, Oct 29 '20 at 15:50
In addition to the problem of namespaces (plural), you also need to pay attention to what your templates match. If you only match the `Table` element, there will be other elements (e.g. `ExcelWorkbook`) that will be handled by the built-in template rules which will copy their text nodes to the output. — michael.hor257k, Oct 29 '20 at 16:08

Yitzhak Khabinsky · Accepted Answer · 2020-10-29T18:04:49.837

0

Please try the following XSLT.

As many folks already mentioned out, it shows the following:

Proper handling of namespaces. All elements in question belong to the namespace with the 'ss' prefix, That's why it is mandatory to specify it. For example, match="/ss:Workbook", and the like.
Proper XPath expressions.

XSLT

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" exclude-result-prefixes="ss">
<xsl:output indent="yes" method="html" />

    <xsl:template match="/ss:Workbook">
        <html>
            <body>
                <h2>University of Colorado Boulder</h2>
                <table border="1">

                    <xsl:for-each select="ss:Worksheet/ss:Table/ss:Row">
                        <tr>
                            <xsl:for-each select="ss:Cell/ss:Data">
                                <td>
                                    <xsl:value-of select="text()"/>
                                </td>
                            </xsl:for-each>
                        </tr>
                    </xsl:for-each>
                </table>
            </body>
        </html>
    </xsl:template>
</xsl:stylesheet>

edited Oct 29 '20 at 18:04

answered Oct 29 '20 at 17:38

Yitzhak Khabinsky

18,471
2
15
21

@michael.hor257k, I definitely tried it on my machine before posting. Do you see any issues? Please be more specific. – Yitzhak Khabinsky Oct 29 '20 at 17:51
I just tried it on my end, it worked! Can you elaborate more on why you select = "ss:Worksheet/ss:Table/ss:Row" instead of just select = "Worksheet/Table/Row"? And thanks again! – ruijiang-ma Oct 29 '20 at 18:00
@ruijiang-ma, glad to hear that the proposed solution is working for you. Please don't forget to mark it as Answer. – Yitzhak Khabinsky Oct 29 '20 at 18:01
I hope I satisfied everybody needs. Peace. – Yitzhak Khabinsky Oct 29 '20 at 18:07
@ruijiang-ma For explanation see: https://stackoverflow.com/a/34762628/3016153 – michael.hor257k Oct 29 '20 at 18:20
@Yitzhak Khabinsky, you sure did! THANKS and happy Halloween! – ruijiang-ma Oct 29 '20 at 18:32

How to transform XML with attributes?

1 Answers1