Excel XML, XPath and XSLT Workflows

XML is a markup language which is designed to contain information in a deliberately neutral fashion. It is extremely versatile and can be used as a container for any information which can be represented as text...

An XML document consists of two principal parts: the prologue and the root element. The prologue is optional; but the root element is compulsory...

An XML document is essentially a tree structure consisting of what are normally referred to as nodes. These nodes are of different types; and perhaps the most important type is the element node...

Continuing our look at the different types of node that you can find in an XML document, in this video, we will move on to look at attributes.

Entity references exist to remove ambiguity from an XML document. When writing XML, you use certain special characters; so, clearly, if you want to use any of these characters in your data, this can create ambiguity...

In the last video, we discussed the use of entity references to enable you to use illegal characters within the content of your XML documents. When you need to make frequent use of such characters, the XML specification offers another approach, called a CDATA section... 

Continuing our look at the various objects which can be placed inside an XML file, we now come to comments... Comments are a useful feature, found in all coding environments, which enables you to insert text anywhere within the document which will not be treated as code and will be totally ignored...

Processing instructions achieve a similar objective to comments, in that they are not part of the XML structure. However, where comments are designed for humans, processing instructions are designed for systems...

We saw earlier that XML enables otherwise incompatible systems to exchange data. However, in order for data exchange to be successful, it is important that the integrity of an XML document can be verified in some way; and this verification is referred to as XML validation...

In order to be considered well-formed, an XML document has to adhere to the seven rules outlined in this video.

DTD stands for Document Type Definition; and it is the first method of validating an XML document. The declarations which specify what is legal within the XML  document constitute the DTD; and these declarations may either be made internally or externally.

In this video, we will move on to look at the second method of validating an XML document, which is to use a schema definition document...

In this video, we discuss the basic technique for importing XML into Excel and some of the ideal structure which and XML file should have if it is to be successfully imported as an Excel XML table.

In this video, we will discuss the role of XML maps in Excel XML workflows.

So far, we've only discussed importing a single XML file; but you may have workflows where you have several XML files that need to be combined into one dataset; or where the XML is supplied to you in batches; so you have to import the data incrementally...

In this video, we will look at validating XML data as you import it into an Excel worksheet.

In this video, we will move on to look at working with an XML document which has a slightly more complex structure than the ones we have seen so far. We will be importing data from different sections of a multi-level XML file into several different tables.

In this video, we will look at importing data from different sections of a multi-level XML file into a single table.

In this video, we will look at exporting data from an Excel workbook. 

This video introduces Microsoft's WEBSERVICE function which is used to get a response from an online XML web service.

Excel's WEBSERVICE document typically returns an XML document into the cell containing the function. The FILTERXML function is then used to extract data from this document; and to do this it uses an XML-related technology called XPath. This video introduces the XPath statements used as the second argument of the FILTERXML function.

This video explains how to use the two Excel functions used in conjunction with the WEBSERVICE function: FILTERXML and ENCODEURL.

Although the FILTERXML function is designed to work with online web services, you may occasionally find it convenient to use it with your local XML files; and, in this video we will look at a workaround for doing just that.

In this video, you will learn how to use an XPath predicate to "home in" on a specific node within an XML file.

In this video, we will examine the use of Excel's new CONCAT function to concatenate an imported XML file, so that it only occupies one cell, as required by the FILTERXML function.

In all the XPath examples that we have seen so far, we have been working with the default child axis. In this video, we will talk about another axis that you will encounter, when working with XPath; the descendant-or-self axis.

The first video in this section provides an overview of the process of applying an XSLT stylesheet to an XML document as it is imported into Excel.

This video explains how to use the xsl:element element, in an XSLT stylesheet, to create new elements in the output XML document.

In this video, we will look at another key element that is used in XSL stylesheets, xsl:copy-of, which is usd to copy elements into the output XML document without any modification.

In this video, we will look at how you can transfer elements from the input to the output XML document but modify the contents that you have inside the element.

In this video, we will look at how you create attributes in XSLT stylesheets.

In this video, we move on to look at the most basic of the two conditional statements used in XSL stylesheets: xsl:if.

In this video, we examine the more powerful of the two conditional statements used in XSL stylesheets: xsl:choose.

We end this section by looking at one of the most elegant and powerful constructs provided by XSLT: attribute value templates.