Embedded XSLT

Domo · 16 Jul 2013 at 10:20

I'm hoping someone can help me create XSLT and embed it into the following XML file. It's a big ask I know. I've not succeeded so far and I'm wondering if it's because the XML uses the MS Office schema.

Code:

<?xml version="1.0" encoding="UTF-8"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Styles/>
<ss:Worksheet ss:Name="Information">
<ss:Table>
<ss:Row>
<ss:Cell>
<ss:Data ss:Type="String">Column Heading 1</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Column Heading 2</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Column Heading 3</ss:Data>
</ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell>
<ss:Data ss:Type="String">Column Data Row 1</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Column Data Row 2</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Column Data Row 3</ss:Data>
</ss:Cell>
</ss:Row>
</ss:Table>
</ss:Worksheet>
</ss:Workbook>

AnDyR0o · 24 Jul 2013 at 17:38

Hi mate,

XSLT is not something i work with very often but this might help you get started. Your request was a little vague so let me know if this is not what you are looking for.

Code:

<xsl:stylesheet version="1.0" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
  <html>
  <body>
    <table border="1">
      <tr>
      <xsl:for-each select="/ss:Workbook/ss:Worksheet/ss:Table/ss:Row[1]/ss:Cell">
        <th><xsl:value-of select="ss:Data"/></th>
      </xsl:for-each>
      </tr>
      <xsl:for-each select="/ss:Workbook/ss:Worksheet/ss:Table/ss:Row[2]/ss:Cell">
      <tr>
        <td><xsl:value-of select="ss:Data"/></td>
      </tr>
      </xsl:for-each>
    </table>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>

Domo · 28 Jul 2013 at 00:44

Hi Andy,
Been on hols but damn happy to see a reply to this thread!
Basically I need to transform the XML into CSV using XSLT so the information can be fed into an application which excepts XML but only when XSLT is used.
The XSLT would be separate from the original XML as opposed to inline.
I'll try your sample out and get back to you - cheers!

Domo

Domo · 28 Jul 2013 at 01:25

This should help clear things up and describes exactly what it is I've been trying to achieve.
XML:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Styles/>
<ss:Worksheet ss:Name="Information">
<ss:Table>
<ss:Row>
<ss:Cell>
<ss:Data ss:Type="String">Make</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Model</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Colour</ss:Data>
</ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell>
<ss:Data ss:Type="String">Ford</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Focus</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Green</ss:Data>
</ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell>
<ss:Data ss:Type="String">Volkswagen</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Golf</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Black</ss:Data>
</ss:Cell>
</ss:Row>
</ss:Table>
</ss:Worksheet>
</ss:Workbook>

The output should look like:

Make,Model,Colour
Ford,Focus,Green
Volkswagen,Golf,Black

By all means message me if needed.

peterwalkley · 28 Jul 2013 at 13:43

Can't you just export it as CSV format from excel?

Domo · 29 Jul 2013 at 00:57

That would be ideal - but the answer is no because it's for an automated feed from a cloud db into a locally hosted application.
The cloud produces the XML file which the app cannot read - hence the requirement for XSLT to perform the necessary transformation since the app can read in CSV.

Gzero · 29 Jul 2013 at 02:19

Domo said:
That would be ideal - but the answer is no because it's for an automated feed from a cloud db into a locally hosted application.
The cloud produces the XML file which the app cannot read - hence the requirement for XSLT to perform the necessary transformation since the app can read in CSV.

VBScript it to save the workbook as CSV? Schedule task to run a bat file looking for the file in a dir and delete the file afterwards?

peterwalkley · 29 Jul 2013 at 10:02

Try this:

Code:

<xsl:stylesheet version="1.0" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="text" indent="no"/>
	<xsl:template match="/">
		<xsl:for-each select="/ss:Workbook/ss:Worksheet/ss:Table/ss:Row">
			<xsl:variable name="row" select="position()"/>
			<xsl:call-template name="processRow">
				<xsl:with-param name="row"/>
			</xsl:call-template>
		</xsl:for-each>
	</xsl:template>
	<xsl:template match="/ss:Row" name="processRow">
		<xsl:param name="rowId"/>
		<xsl:for-each select="ss:Cell">
			<xsl:value-of select="ss:Data"/>
			<xsl:choose>
				<xsl:when test="position()=last()">
					<xsl:text>
</xsl:text>
				</xsl:when>
				<xsl:otherwise>,</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

I'm just using character 10 for newlines rather than the windows CR-LF sequence.

I'm sure there's a cleaner way with just template matching on rows that doesn't need the explicit for-each-row in the outer template, but my XSL-foo is failing me at the moment.