XML Transform

XSL - is XML stylesheet language.

XSLT is a simple language containing looping, decision making, evaluation, branching and functional statements
It follows the same conventions as XML and its sole purpose is to interpret and transform valid XML documents into different forms.

Creating an XSLT

You can use XSLT to perform a wide variety of transformations such as:

• Extract specific items from a spreadsheet

• Transform a spreadsheet into HTML

• Make changes to a spreadsheet

• Highlight significant changes.

You can create an XSLT file using notepad.
Perform the transformation.

Transforming XML

There are several ways you can transform an XML file

• Using the Command Line

• Using a Browser

• Using VBA Code

Using the Command Line

It is possible to perform the transformations using the command line utility (msxsl.exe)
This is free to download from the Microsoft, MSDN website.
This program is a small shell executable that calls the Microsoft XML Parser to perform the transformation.
The following command line transforms the file XMLBefore.xml to produce the file XMLAfter.xml

msxsl XMLBefore.xml strip.xslt -o AfterXML.xml 

Can perform batch transformations
Command line utility must be downloaded.

Using a Browser

You can also perform XML transformations by using a browser by including an XML stylesheet processing instruction.
The following shows the changes you must make to the XML file.

Now when the user opens the file, the browser will transform the contents automatically to display the results.
Not all non Microsoft borwsers support this technique.

?xml-stylesheet type="text/xsl" href="worksheet.xslt"? 

Dynamically transforms the file on the client when viewed in the browser.
Changes in the XSLT are reflected immediatley - but it doesn't work in all browsers.

Example - XSLT - worksheet to HTML

Using VBA Code

Using the Microsoft XML Object Library
Microsoft XML Parser msxml14.dll
This provides the DOMDocument object
XMLDOMNodeList ??

Dim xdoc As New DOMDocument 
Dim xstyle As New DOMDocument 
xdoc.Load (ThisWorkbook.path & "\TestStrip.xml") 
xstyle.Load (Thisworkbook.path & "\Strip.xslt") 
xml = xdoc.transformNode(xstyle) 

This can be used to create new xml files, navigate between nodes, copy, delete and add nodes.

You can also use XSLT to transform XML files created outside of Excel into XML spreadsheets
It is recommended that if you are performing an extensive transformation it is easier to break up the steps into separate templates that are then called from one master template.

Lets consider a simple Invoice .