O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using XSLT on Import or Export

Problem

I'd like to transform my data using XSLT while I'm importing or exporting. Where do I begin?

Solution

XSLT (Extensible Stylesheet Language Transformations) is a powerful and complex language that you can use to transform your data, including both the values and the XML tags used. An XSLT transform (OK, I really mean "program," but an XSLT program is unlike anything you might create in any other programming language) is written in XML.

Let's assume that your XML data looks like this:

	<?xml version="1.0" encoding="UTF-8"?>
	<dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
	generated="2006-01-30T22:11:30">
	  <Query7-7>
	    <Date>2005-12-01T00:00:00</Date>
	    <DowJonesClose>10717.5</DowJonesClose>
	    <NASDAQClose>2205.32</NASDAQClose>
	  </Query7-7>
	  <Query7-7>
	    <Date>2005-11-01T00:00:00</Date>
	    <DowJonesClose>10805.87</DowJonesClose>
	    <NASDAQClose>2232.82</NASDAQClose>
	  </Query7-7>
	</dataroot>

But, say the system to which you're sending your data doesn't like the format of the date. Instead, it wants dates to be formatted this way:

	<Date>
	  <Year2006</Year>
	  <Month>01</Month>
	  <Day>30</Day>
	</Date>

You can easily accomplish this change using this XSLT transform:

 <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <StockInfo> <xsl:apply-templates/> </StockInfo> </xsl:template> <xsl:template match="Query7-7"> <Row> <Date> <Year><xsl:value-of select="substring(Date,1,4)"/></Year> <Month><xsl:value-of ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required