Microsoft Excel is a very XML aware application, featuring not only XML, but also XPath and XSLT capabilities; and this intermediate/advanced Excel course explains all of Excel's XML-related features in depth. The course assumes a good familiarity with Microsoft Excel and the use of Excel formulas. However, no knowledge of XML, or its related technologies, is assumed.
The course begins with an overview of the nature of XML and itemizes the different objects which can form part of an XML document. This introductory section also explains the use of DTDs and schema documents to validate XML.
We then move on to look at the importing and exporting of data into and out of Microsoft Excel. We discuss the creation of XML maps and how they relate to XML tables, as well as the validation of XML data, using schema documents, both on import and export.
Next, we explore online XML, as we use Excel's WEBSERVICE, FILTERXML and ENCODEURL functions to download XML data from online web services and use XPath statements to extract the information we want from the downloaded XML.
Although FILTERXML and its XPath statements are designed to work only with online web services, this course will show you a crafty workaround which will enable you to use this function to extract data from your local XML documents as well.
The final section of this course is given over to an XML capability which is well hidden in Microsoft Excel, namely the ability to transform XML as you import it by using XSL stylesheets. The course provides an overview of how XSL sheets are constructed and how XPath is used within stylesheets to target the different parts of the input XML document which you want to transform and include in the output.
In short, whatever Excel XML workflows you are faced with, this course will equip you to deal with them confidently and efficiently.
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.
Grant Gamble is an experienced IT trainer, developer, consultant and author able to deliver a wide range of training courses. He has a vast experience of delivering public and on-site IT training content at different skill levels, to groups of varying sizes.
His UK company G Com Solutions Limited provide IT training courses and consultancy to a wide range of UK and international clients. His speciality is running week-long, intensive training workshops on topics like Microsoft Power BI, VBA, web development and Adobe Creative Suite automation.