SSDN - Generate an XSLT from an XML file for subsequent use on other XML

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Topic Page 1 2 3 4 5 6 7 8 9

Gregory McKenzie

Subject: Generate an XSLT from an XML file for subsequent use on other XML
Author: Gregory McKenzie
Date: 10 Oct 2007 11:55 AM

We are looking at taking an XML definition of a data table, generating an Excel XSLT from the XML definition of the data table. The generated Excel XSLT is then to be used on serialised data that is stored in the data table.

XML Definition of Data Table
----------------------------

Provided below is a cut down version of an XML definition of a table.

<SQLObjects>
<SQLObject Name="Delegate" ObjectType="tbl">
<Field Name="DelegateID" SQLType="int"/>
<Field Name="RecordedDate" SQLType="datetime"/>
</SQLObject>
</SQLObjects>

Generating an Excel XSLT from XML
---------------------------------

The Excel XSLT that we want to create takes the XML definition of the table and generates an XLST output that can then be used on XML data to output an Excel XML document. An example of the a cut down version of the Excel XSLT we are *trying to generate* is provided below.

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:output method="xml" indent="yes" encoding="UTF-8"/>
<xsl:template match="ArrayOfConferenceDelegate">
<xsl:processing-instruction name="mso-application">
<xsl:text>progid="Excel.Sheet"</xsl:text>
</xsl:processing-instruction>
<Workbook>
<Worksheet ss:Name="Delegates">
<Table>
<Row ss:Index="1">
<Cell ss:Index="1">
<Data ss:Type="String">DelegateID</Data>
</Cell>
<Cell ss:Index="2">
<Data ss:Type="DateTime">RecordedDate</Data>
</Cell>
</Row>
<xsl:for-each select="Delegate">
<Row ss:Index="{position()+1}">
<Cell ss:Index="1">
<Data ss:Type="String">
<xsl:value-of select="DelegateID"/>
</Data>
</Cell>
<Cell ss:Index="2">
<Data ss:Type="String">
<xsl:value-of select="RecordedDate"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>

Applying Generated XSLT to XML
------------------------------

The generated Excel XSLT will then be applied to serialized data stored in the table. An example of the XML data on which the XSLT is applied is provided below.

<?xml version="1.0"?>
<ArrayOfDelegate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Delegate>
<DelegateID>1</DelegateID>
<RecordedDate>1900-01-01T00:00:00</RecordedDate>
</Delegate>
<Delegate>
<DelegateID>7</DelegateID>
<RecordedDate>1900-01-01T00:00:00</RecordedDate>
</Delegate>
</ArrayOfDelegate>

The XML above is transformed into an XML Excel document.

Issues
------

(A) Creating the XSLT that generates the Excel XSLT from the XML definition of the data table. The problems are outputting tags such as '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">'.

Have identified some useful commands along the was such as:

<xsl:processing-instruction name="mso-application">
<xsl:text>progid="Excel.Sheet"</xsl:text>
</xsl:processing-instruction>

but cannot work out how to generate all of the XSLT tags using XSLT.

(B) Namespace issues - the generated code has the Excel namespace tags on each of the tags generated.

Current XSLT that Generates Excel XSLT
--------------------------------------

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"/>
<xsl:template match="/">
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Font ss:FontName="Arial" ss:Size="12"/>
</Style>
<Style ss:ID="Bold">
<Font ss:FontName="Arial" ss:Bold="1"/>
</Style>
<Style ss:ID="Date">
<NumberFormat ss:Format="yyyy\-mm\-dd\ hh:mm;@"/>
</Style>
</Styles>
<xsl:for-each select="SQLObjects/SQLObject">
<Worksheet ss:Name="{@Name}">
<Table>
<Row ss:Index="1">
<xsl:for-each select="Field[@SQLType!='uniqueidentifier']">
<Cell ss:Index="{position()}">
<Data ss:Type="String">
<xsl:value-of select="@Name"/>
</Data>
</Cell>
</xsl:for-each>
</Row>
<Row ss:Index="">
<xsl:for-each select="Field[@SQLType!='uniqueidentifier']">
<Cell ss:Index="">
<Data ss:Type="String">
<xsl:attribute name="xsl:value-of">
<xsl:text>select='</xsl:text><xsl:value-of select="@Name"/><xsl:text>'</xsl:text>
</xsl:attribute>
</Data>
</Cell>
</xsl:for-each>
</Row>
</Table>
</Worksheet>
</xsl:for-each>
</Workbook>
</xsl:template>
</xsl:stylesheet>

Current Excel XSLT Generated from XSLT
--------------------------------------

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Styles xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Style ss:ID="Default" ss:Name="Normal" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Font ss:FontName="Arial" ss:Size="12" xmlns="urn:schemas-microsoft-com:office:spreadsheet"/>
</Style>
<Style ss:ID="Bold" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Font ss:FontName="Arial" ss:Bold="1" xmlns="urn:schemas-microsoft-com:office:spreadsheet"/>
</Style>
<Style ss:ID="Date" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<NumberFormat ss:Format="yyyy\-mm\-dd\ hh:mm;@" xmlns="urn:schemas-microsoft-com:office:spreadsheet"/>
</Style>
</Styles>
<Worksheet ss:Name="ConferenceDelegate" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Table xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Row ss:Index="1" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Cell ss:Index="1" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Data ss:Type="String" xmlns="urn:schemas-microsoft-com:office:spreadsheet">DelegateID</Data>
</Cell>
<Cell ss:Index="2" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Data ss:Type="String" xmlns="urn:schemas-microsoft-com:office:spreadsheet">RecordedDate</Data>
</Cell>
</Row>
<Row ss:Index="" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Cell ss:Index="" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Data ss:Type="String" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xsl:value-of="select='DelegateID'"/>
</Cell>
<Cell ss:Index="" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Data ss:Type="String" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xsl:value-of="select='RecordedDate'"/>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

Best Way Forward?
-----------------

Should we change our output method to TEXT instead of XML and generate the Excel XSLT that way. Alternatively, does somebody have an example of how we can use XSLT to generate XLST from XML?

(Deleted User)

Subject: Generate an XSLT from an XML file for subsequent use on other XML
Author: (Deleted User)
Date: 10 Oct 2007 12:30 PM

Hi Gregory,
what you are trying to do is called "meta stylesheet", where an XSLT stylesheet has as output another XSLT stylesheet. The trick to make it work is to generate the XSLT keywords in a different namespace (like out:stylesheet, with "out" assigned to the "xxxxx" namespace URI), and then have a xsl:namespace-alias instruction to convert it to the real one.
See http://www.xml.com/pub/a/2003/11/05/xslt.html for a nuce tutorial on this technique.

Hope this helps,
Alberto

Topic Page 1 2 3 4 5 6 7 8 9

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.