Excel XML, XPath and XSLT Workflows
4.8 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
38 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel XML, XPath and XSLT Workflows to your Wishlist.

Add to Wishlist

Excel XML, XPath and XSLT Workflows

Extend your advanced Excel skills with XML and XSLT processing
New
4.8 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
38 students enrolled
Created by Grant Gamble
Last updated 9/2017
English
Current price: $10 Original price: $125 Discount: 92% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 4 hours on-demand video
  • 1 Article
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • You will learn the basics of XML and the role played by DTDs and schema documents in XML validation.
  • You will learn how to import and export XML data into and out of Microsoft Excel.
  • You will learn how to use XML mapping to target specific parts of the XML documents you are importing.
  • You will learn how to use the WEBSERVICE function to retrieve data from an online web service.
  • You will learn how to use Excel's FILTERXML function to extract data from both online and local XML data sources.
  • You will how to construct XPath statements to target specific locations within an XML document.
  • You will learn how to use XSLT stylesheets to reshape XML documents into an Excel-friendly structure.
  • You will learn the basic syntax used in XSLT stylesheets and how XPath is used within XSLT.
View Curriculum
Requirements
  • Microsoft Excel 2016, 2013, 2010 or 2007.
Description

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.

Who is the target audience?
  • This course is designed for experienced Excel users who are used to working with formulas and functions. No knowledge of XML, XPath or XSLT is assumed.
Compare to Other XSLT Courses
Curriculum For This Course
36 Lectures
04:10:27
+
1. Getting Started
2 Lectures 02:56

2. Download the course files here
00:08
+
2. XML Essentials
12 Lectures 01:24:48

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...

Preview 04:11

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

Preview 05:06

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...

Preview 06:05

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.

Preview 08:38

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...

5. Entity references
07:42

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... 

6. CDATA sections
06:41

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...

7. Comments
03:04

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...

8. Processing instructions
08:16

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...

9. XML Validation
02:36

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

10. Well-formed XML
12:46

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.

11. Overview of DTDs
09:56

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...

12. Overview of XML schema documents
09:47
+
3. Excel and XML
7 Lectures 48:32

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.

Preview 07:28

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

2. Creating an XML map
07:44

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...

3. Importing XML data incrementally
05:19

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

4. Validating XML data
04:07

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.

5. Multilevel import multiple tables
07:45

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

6. Multilevel import single table
09:55

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

7. Exporting XML data
06:14
+
4. WEBSERVICE, FILTERXML and XPATH
7 Lectures 52:14

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

1. Using the WEBSERVICE function
10:05

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.

Preview 04:46

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

3. Using FILTERXML and ENCODEURL
12:18

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.

4. Using FILTERXML with local XML files
08:49

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

5. Using predicates
04:56

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.

6. The new CONCAT function
08:14

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.

7. Descendant-or-self axis
03:06
+
5. Using XSLT stylesheets
8 Lectures 01:01:57

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.

Preview 11:28

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

2. Creating new elements
12:03

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.

3. Using xsl-copy-of
07:36

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.

4. Using xsl-text and xsl-value-of
05:40

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

5. Creating new attributes
09:22

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

6. Using xsl-if
04:49

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

7. Using xsl:choose
04:20

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

8. Using attribute value templates
06:39
About the Instructor
Grant Gamble
4.5 Average rating
502 Reviews
13,461 Students
8 Courses
Developer, trainer and author

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.