Excel XML, XPath and XSLT Workflows | Grant Gamble | Skillshare

Excel XML, XPath and XSLT Workflows

Grant Gamble

Excel XML, XPath and XSLT Workflows

Grant Gamble

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
35 Lessons (4h 10m)
    • 1. 01-01: Welcome to Excel XML, XPath and XSLT Workflows

    • 2. 02-01: What is XML?

    • 3. 02-02: Structure of and XML Document

    • 4. 02-03: Elements

    • 5. 02-04: Attributes

    • 6. 02-05: Entity References

    • 7. 02-06: CData Sections

    • 8. 02-07: Comments

    • 9. 02-08: Processing instructions

    • 10. 02-09: XML Validation

    • 11. 02-10: Well formedness

    • 12. 02-11: Overview Of DTDs

    • 13. 02-12: Overview of XML Schemas

    • 14. 03-01: Importing XML into Excel

    • 15. 03-02: Creating an XML Map

    • 16. 03-03: Importing XML Data Incrementally

    • 17. 03-04: Validating XML Data

    • 18. 03-05: Multilevel Import Multiple Tables

    • 19. 03-06: Multilevel Import Single Table

    • 20. 03-07: Exporting XML Data

    • 21. 04-01: Using the Webservice Function

    • 22. 04-02: XPath Overview

    • 23. 04-03: Using FILTERXML and ENCODEURL

    • 24. 04-04: Using FILTERXML with Local XML Files

    • 25. 04-05: Using Predicates

    • 26. 04-06: The New CONCAT Function

    • 27. 04-07: Descendant or Self Axis

    • 28. 05-01: XSLT And Microsoft Excel

    • 29. 05-02: Creating New Elements

    • 30. 05-03: Using XSL

    • 31. 05-04: Using XSL-text and XSL-value-of

    • 32. 05-05: Creating New Attributes

    • 33. 05-06: Using XSL-if

    • 34. 05-07: Using-XSL-choose

    • 35. 05-08: Using Attribute Value Templates

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class


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.

Meet Your Teacher

Teacher Profile Image

Grant Gamble


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.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.



1. 01-01: Welcome to Excel XML, XPath and XSLT Workflows: uh, hello and welcome to Microsoft Excel XML experts and excess lt Work flows My name's ground gamble on the train on this course on what will cover is basically just about everything you could do with Microsoft Excel involved in the manipulation of external data. Microsoft Excel is a very XML aware application, and there are quite a few things that you could do involved in the manipulation of XML. We'll start the calls by covering the basics of XML. So for those of you who haven't worked with exceptional very much before, you may find this useful. If you've worked with external before, you know Alexis works. Feel free to just get this section and move onto the next one, where we cover the import and export operations within Microsoft Excel. We'll look at bringing in XML data on the creation of external maps on the way that you can link XML tables to an extent Man in the next section will move on to locate Microsoft's Web service, filter external functions, which enable you to query online XML services, return an external document and then extract data from any part of that document. Use in ex parte statements, I will talk about the way that experts statements are constructed. Although these functions are meant to work only with online services, there is a work around which permits you to use them on your own local external files. So we'll spend some time looking at that work around on just learning how to use expert statements in much the same way as you could use. V. Look up for ordinary Excel data. We look at how you can use Filter XML to query a local XML file and call in the data that you need for your solutions. In the final section, talk about one of excels less unknown external beaches, which is the ability to apply an excess lt style sheep as you import XML Documents came to Microsoft Excel well, if it the way that excess lt documents are constructed and we'll learn how to use expert statements to navigate your way through the Input XML document as you construct your help with exiled argument. So whatever XML work flows you're faced with this calls will equip you to deal with them competently and efficiently. 2. 02-01: What is XML?: XML is a markup language which is used to contain information in a deliberately neutral fashion. It's extremely versatile and it could be used as a container for any information which can be represented as text. This neutrality allows XML to enable communication between systems and environments which have little or nothing in common. For example, let's say we have four systems that need to talk to each other and exchange data. If they send data directly to each other, they're unlikely to be discrepancies and errors. Whereas if all the systems are XML aware by he's in XML, they're all on the same page and they know that when they exchange data, they're going to get controllable and predictable results. It's important to point out, however, that the phrase using XML doesn't employ that there is only one version of XML. XML doesn't have a fixed, preset vocabulary, which everyone has to use. It allows developers to define the names of the elements on the rules governing the data contained within these different elements. So in our example, the four systems in question wouldn't just be used in except Mount to exchange data. There would be using a specific XML vocabulary with a known set of elements and a known set of rules as to how content has to be these crime within the XML documents that they exchange between each other. Another common scenario is where organisations have toe output their corporate data in lots of different media. Rather than setting up a separate workflow for each medium in which they want to output data. They can use XML as the definitive version off their data and then Macon set up work flows from XML to the different media. So XML toe print XML to e book XML to pdf XML to the World Wide Web and so forth. An XML document is essentially a text document and it can be opened in any text editor. So let's go into the 02 folders, you know, to XML Essentials and then 01 what is XML and just open up the file called basic dot XML and the key things to note our festival that it's human readable. We can see the data itself. Seven see people's names, people skills and we can deduce the function of each of the pieces of data. Looking at the names of the tags, which surround each piece of data. We can also see that the document has a hierarchical structure. This is emphasized by the editor. So here in brackets you'll notice that we have collapsible arrows next to allow those elements, which contains other elements, so we can see that there are essentially three levels at the top level, we have staff. Levi collapsed are you'll see that the entire document is collapsed. Then, within the staff element, we have a series of staff member elements, and again, each of these congee collapsed and expanded within each staff member element. We have first name, last name skills and photo, and then inside each of these, we have the actual data. That's an overview of what XML is all about. In the next video, we'll move on to talk in more detail about the structure of XML documents. 3. 02-02: Structure of and XML Document: An XML document consists of two principal parts. The prologue and the root element. The prologue is optional that the root element is compulsory. The product contains information about the XML document as a whole. The root element contains all of the content off the XML document. Let's take an example, have created the simplest possible XML document in zero to XML Essentials. Open up number two structure of an XML document, and inside it you'll find a file called Hello well, that XML open that in your editor. Let's go ahead and create our file to create a valid XML file. The only content we need to place in the file is the root element. The name of the root element can be anything you like in XML. It's entirely up to you to define the names off the elements within your external structure . But for simplicity, let's call it route to write an element. We put an opening tag, which consists of the left and bracket, the name of the element on the right angle bracket. And in the case of most editors, the closing tag will automatically be inserted for you anything between the opening and closing tanks is regarded as the content off. The element on that content could be very varied, and we'll discuss the various types of content in the rest of this chapter, and you'll notice that there's a distinction between the way that the opening tank is written on the closing tag. The left angle bracket of the closing tag is followed by a forward slash. So in our super simple example, let's just put Hello World and that, believe it or not, is now a valid XML file. To put it to the test, you can either use the XML validator built into your editor. Or if your editor doesn't have an XML validator, you'll find the U. R L in this file. External validation, which you can just copy and paste into your browser. And then we can just paste it into this box control. Be and click on validate, and you can see we have landed XML. Let's just go back to the editor and let's add in the optional prologue. The only compulsory element within a prologue is the XML declaration, and it looks like this left and bracket question Mark XML, and then the only thing we have to specify is the version, which is one point north, and we can then optionally specify the encoding, which for English is utf eight. And then we close the declaration with question mark on a right angle bracket. And let's just check that out. XML file is still valid. Control me to paste on Valley Days, and as you can see, we still have valid X amount. So that's a quick look at put in the bare bones, the two essential elements into an XML file. In our simple example, we put text straight into the root element. This is fairly unusual. What typically you'll have a hierarchy off child elements within the root elements, and you have to drill down several levels deep before you find the actual textual content. As you can see from this diagram, there are a number of different types of objects that could be placed both inside the prologue and in the root element, and we'll be discussing these various objects throughout the rest of the chapter. In the next video, we'll take a closer look at what is perhaps the most important of these objects. The XML element 4. 02-03: Elements: an XML document is essentially a hierarchical tree structure, consistent and what are normally referred to. As knows, each node is of a particular type and perhaps the most important type. Certainly the most frequently encountered within the tree structure is the element load elements have to may functions. The first is to define the structure of the XML document by containing other knows, in this example, the root element. It's called tools, and within this route element, we have the tour element, and this would typically be a repeated element inside each tour element. In turn, we have name, destination region on price, and it's inside these child nodes that we find the actual text, the main content that the XML document. This is the logical structure of the XML document. Let's now look at how that structure would be written in our training folder were working in zero to Excel. Many Central's Let's now go into sub folder three elements. If you'd like to have a go it creating the XML document for yourself. Double click on the file call tools don't J. Peg. I just use that as a guide and then see if you can actually create this from scratch. If you'd like to do that, just pause the video on, then go ahead and have a go of lice. Let's do it together. It's just double take on tools that XML, which is just a blank file. Let's begin by putting in the XML declaration. Okay, Okay. When creating an XML document, it's normal toe work in the same hierarchical fashion as we can see in this diagram. So we start by creating the root element, which is we've said it's called tools. XML is case sensitive, so it will work into a specification. We need to take that into account, say most editors. If you create the opening tag as soon as you finished it, the closing tag has created for you automatically, and here we can just press enter twice. Press the up arrow to position the cursor between the opening and closing tax of the root element. A bad press, the tab key to in dent, indicating that what we're about to create is an element inside tools, and this is done purely for readability. It makes absolutely no difference how much mind space you put into an XML document and next we need the tool element and again we'll create a blind line moved back up and tabbing to indicate a child element. Then we have name, Destination, region and Price. I've just pressing the end key to get to the end of the line each time. And finally we have our text. Five day guided tour, San Diego, South America 2595 Okay, and let's say we now want to create another tour element. Obviously we do the logical thing, which is to copy this entire structure, paste it and make the necessary changes. Let's say we have another five day guided tour to Caracas again in South America, but slightly Chiba. So things to note elements can either contain child elements. All the actual text, your content of an XML document elements and normally written by placing an open intact among the start of the element content and the closing tag to indicate where the content actually ends. Any text or any elements place between the opening tag on the closing tag are the contents or Children off that element. Elements then are perhaps the most important no type within an XML document. In the next video, we'll move on to look at what is probably the second most important after abuse 5. 02-04: Attributes: continue Now look at the different types of node that you can find in an XML document in this video will move on to look at attributes on how attributes differ from elements we saw in the previous two videos. But elements are the most important nodes within XML documents they used both to contain on their elements and to contain the actual data that the XML document is. Story. By contrast, attributes are normally used to store metadata. That is to say, information about your data here in the tools example which we worked with in the last video. We want to add an a tribute to the tour's element, the apparent element called Season On. We want to give that attribute a value of 2017 on. We're doing this because we regard it is meta data. It's information, which is important, but which isn't part of the data set. That's, for example, if this XML was going to be used to produce a brochure, the season probably wouldn't appear as part of the data. That's what we're implying by using season as an attribute rather than having it is an element which contains data and let's take another example, we have a price element. Let's say that we want to clarify that the currency being used is pounds sterling tpp. But we don't necessarily want to indicate this in the brochure itself. So again we have metadata rather than data. And for that reason we want to implement. This is an attribute rather than as an element. So that's the logic behind that tributes. Let's now have a look at how they're written. Attributes have written inside the Oaten in tag often element. So this generic example. We have an element called My Element, and we want to assign an attribute to it called My Attribute. In the opening tag we write, my attributes equals. And then, in double quotes, we put the value that we want to assign to the attribute. Then we have the content of the element, and we have the clothes in tag. It's when you implemented an attribute, only the open in tag off. The element is affected. Nothing happens to the content, and nothing happens to the close. In tack. Let's get some practice on. Working with attributes in zero to XML essentials, open up some folder Number four attributes And then let's open tools dot xml, which is the file that we created in the last video. And let's create the two attributes that we've just been discussing. The first was season on. We want to put this into the root element tours simply click after the name of the element , put his space and then put the name of your attributes. In this case, we call in its season equals. The spaces are optional, input in them in for readability only. And then in double quotes, we put out 2017. And then let's do the same for price. Nothing happens to the close in Tak. It's always the opening tag. We click after the name of the Element, which is price but a space. And then we put our attribute. This time we want currency equals, and we always use the double quotes CBP. So let's say about changes. The very first example of XML that we looked at on this course was a file called Basic. And here's a diagram representing the XML structure in that file, and you'll notice it contains two attributes. We have an attribute inside the staff member element called department and We also have an attribute inside the photo element called Age Ref. There's one important difference between the stump member on the photo element. Staff member has contents. It has child elements. Photo has no child elements and it has no textual content. Theeighties ref attribute is its only content. This type of element is normally referred to as an empty element. Empty implies that it has no child elements and no textual content, empty elements that have an attribute normally written with one tag only. So using generic syntax, we would say my element space, my attributes equals. And then, in double quotes, we have the value. And then to indicate that this tag is both an opening and closing tag, we put it forward slash before the close in angle bracket. Let's have a look at the file to remind ourselves in zero to XML essentials. Some folder four attributes Couple quick on staff dot xml and this is what the Fire looks like is our first attribute in the staff member Element department equals accounts. Staff member is an element that has child elements, but in contrast, photo has no trial elements, so you'll notice that it's written as a single tag. Hey, Trev equals then we have a file path, and then we have a forward slash in front of the close in angle bracket. So that's the difference. Let's just closed the file. Let's implement the same structure in our tools. Example. You know, folder. You'll notice that we haven't images Sub folder and inside. We've got a couple of photos matching the two destinations that we've inserted into our XML so far, and I'd like to say a quick thank you to pick sabei dot com for supplying these energies. So let's go back to our XML on Let's add in images element to our structure, says Reach to We have image. And then we'll put an attribute, which points to the photo. We can call the attributes anything we like. I'm going to call it S R C, which is taken from HTML equals quotes and then I'm just gonna put a relative path to the file. So images is the name of the folder forwards. Laugh, Santiago, don't drink, Peg. And then to finish off, we put forward slash on the clothes in angle brackets and then we could just copy this whole line three clicks to select the entire line, and then we can copy by use in control and drag. This works in most editors apologies. If it doesn't work in the one you're use it. And here we just change Santiago to Caracas, and that completes our look at the use of attributes and just to summarize. Attributes tend to be used for metadata rather than data. The attributes is always inserted into the open in tag oven element. When the element has no content, a single tag is used. The attribute is inserted into that single tag on. Then the single tag is closed with forward slash, followed by the close in angle bracket. And just to point out that currency equals G V, P will probably be inserted on a low price elements. Even though I didn't do this in the demonstration. 6. 02-05: Entity References: entity references exist to remove ambiguity from an XML document. As we've seen my right in XML, he used certain special characters. The left angle bracket and right angle bracket are used to enclose elements. When you define an attribute, the value of the attributes has to be placed inside quotation marks. By the way, both double and single quotation marks are permissible so clearly, if you want to use any of these characters in normal usage within your data, this concretely eight ambiguity and the main function of entity references is to remove these ambiguity the most typical way of writing an entity. Reference issues in these three components m percent the name of the entity and then a semi colon. An XML has five built in entities designed to remove any potential ambiguity from your XML Marca. The less than sign has the name lt greater than GT double quotation marks. Quote. The ampersand is, um, and apostrophe is a pause. Let's get some practice on using entity references in zero to X Emily Central's Let's Go into Number five entity references. Let's begin by opening this Jay Pek file in design XML Doctor A pig. So the scenario we're going to simulate is where we want to submit. A tutorial to a website on the tutorial has to be submitted in XML format. This is a diagram showing the XML structure that's required. The root element is called tutorial and inside this building and also a title and then one or more topic elements inside each of the topic elements. We must start with a title and then we can have as many parent elements as we want next. Let's have a look at the raw text that we're going to convert into the XML. That's tutorial dot txt is our title. He is our author and he has the title off our topic. We've only got one topic element this time, and then each of these would be inside a parent element. Let's go ahead and create the XML file open up to you to real dot XML and here we've got a blank XML file, so we'll start with the XML Declaration Next, the root element tutorial if we will work into a specification. XML is case sensitive, so we need to take the case into account and then we'll leave some space for the child elements next to want the title of the editorial Also on the topic inside the topic, we then have a title and insight that we then have a series of para elements and I'm just gonna copy this line three clicks to highlight the whole line Control C and control the and I think from memory We need five of these. So I've just got five power elements and next we just copy the text from our plain text file. Is the title control Visa pace? No, right. Take available in brackets. And when we come to the title inside the topic and then we've got to think about the paragraphs. So this point, it would be good to address the question off these illegal symbols. So what we need to do is to replace each of them we have, I think three of the five we've got greater than less Stan Onda double quotes. So we need to replace every occurrence of these three symbols with the entity references. I'm just using note pad, but I'm sure that whatever editor you're use in as the fine and replace command so less than will be replaced with ampersand. Lt for less than and then semi colon replace all greater than will be replaced with ampersand. G t semi colon replace all and finally quotes double quotes We replaced with ampersand. Quote semi couple and replace all. So now we safe so we could start copying across our paragraph elements on just control V to paste each of them in. So let's say that changes and fingers crossed. If we haven't missed anything, that should now be a valid XML document. So let's just move back to the training folder. Let's move back into structure of an XML document on its Remember in XML validation, we have the Earl of Code beautify so we could just copy that going to a browser and then we can validate our XML. So he called me a liar. XML control, See or edit copy control V to paste it in on validates. Did you see that we have valid XML 7. 02-06: CData Sections: In the last video, we discuss the use of entity references to enable you to use illegal characters within the content of your XML documents where you want to make frequent use of these characters Within an XML document, the XML specification offers another approach called a C data section. See data stands for character data, and what it does is to enable you to create an entire section in which you can use illegal characters as many times as you wish. All sections within your XML document normally treated his PC data or past character data. And when rent by an XML passer, illegal characters can only be used within XML marker. Your data cannot contain any illegal characters. But by using C data sections, you're telling the XML Parsa that this particular section contains character data only. There is no markup within this section. Once the pass, it knows that it will automatically converted legal characters to the corresponding entities. This is the syntax that's used to demarcate a C data section. The open intact consists of a left angle bracket, followed by an exclamation mark and square brackets. On the key word see data, another case and then it ends with another opening square bracket. When to terminate a C data section, you simply close the two square brackets and then close the open in angle bracket with a corresponding close in angle bracket between the opening and closing tags. You can then have whatever characters you like. So in this example, we've got the Hello World XML document that we created a few videos back and because it's nested inside a C data section, all the legal characters which normally wouldn't be allowed Ah, um, is it so Let's get some practice on Working with C data sections in zero to XML Essentials . Let's open up six C data sections. Are we going to stay with the same tutorial theme that we saw in the last video? So if you open up tutorial that J. Peg, this is the XML structure, but we're working inside. They have Tutorial is a ruse element, and the child elements are author, title and topic. Then inside the topic element, we have a title which is the first child, followed by as many parent elements as we want. But this time we want to submit a tutorial on excess. Lt So let's open up tutorial dot txt So we've got a tutorial fragment which is you can see includes an XML document. This is, in fact, a very short excess lt style sheet. So rather than convert in all of these illegal characters into entity references, what we can do is to demarcate the section as a C data section. So let's create our XML document. That's open up tutorial dot xml. This time, I've created a skeleton off the document for you since we've already practice creating this structure. So let's just copy in First of all, the title element Control V, the author now the topic title. So here we have two illegal characters. It's manually changed them into entity references. So we have ampersand lt for less than semi Coolum. And then here come percent GT for greater than semi cola. Then we can copy that across. And now we have two paragraphs. No problems here and finally we have our code which we want to embed inside a C data section. So the syntax is left angle bracket, exclamation mark open square brackets. See data in upper case square brackets once more that I'll use control V to paste. Now I need to close square brackets twice because I've opened square brackets twice and then put the right angle bracket to finish. So there's my see data section, and if all is well, I should have a valid XML file. So let's go across to code Beautify Dog. We've got the U. R L in the tractor, too, so just copy that you're l and paste it into your browser. Let's grab our code on controlled vita paste in our code and validates. And as you can see, we have pilot XML. 8. 02-07: Comments: continue. Now look at the various objects which could be placed inside an XML file. We now come to comments and you'll notice that comments can be placed both in the prologue and also anywhere inside the root element. Comments are useful feature found in all coded environments, which enabled you to insert text anywhere within the document, which will not be treated as code and will be totally ignored. So comments of purely for the reference of the person working with the XML file to look at how comments are implemented in XML in zero to XML essentials. That's going to number seven comments and I know tools dot xml to insert a comment in the prologue. Place the comment after the XML declaration, it can't proceed the XML declaration and above the rigs element in terms of the appearance of a comment. If you worked with HTML XML comments are identical left angle brackets, exclamation mark, two hyphens, then your comments and then to close to hyphens and a right angle bracket. So in this document, we might say these files can only contain five day tour listed, so that's an example of a comment in the prologue and I'm just gonna copy that. And let's place one in the root element. For example, Special offer expires with the August. Another frequent use of comments in all code in environments is to comment out a section off your code. For example, if we didn't want this element to be taken into account when we use the XML file on this occasion rather than delete in this section, we could simply turn it into a comment so it will be ignored when any XML process in its done so. All we need to do is to put the open in tax here, but closing tags where we want the comment to stop, and then everything between the opening and closing tax effectively becomes a comment and is therefore temporarily disabled. And that's all there is to an XML. Comment. If you've encountered comments in your HTML code in as you can see, a completely identical in XML 9. 02-08: Processing instructions: process in instructions like comments which we looked at in the last video can be placed anywhere within an XML document in the prologue or anywhere within the Intel Ament, and they achieve a similar objective to comments. They're not part of the XML structure. However, where comments are designed for humans process in instructions a design for systems. They provide a mechanism for XML developers to insert within the XML structure instructions which will only be noted by the systems process in the XML. Generically speaking, this is how processing instructions written. We have the left angle bracket with a question mark and to close behind the question mark with a right angle bracket. Inside, we have what's referred to as a target, which is essentially the name of the process in instruction. And then we have the content, the actual instructions, the most common form of content, his attributes and values. So we have attribute one equals value. One attribute to equals value to and so forth. By far the most widely used process and instruction is the one which links an XML file to an excess lt style sheet, and this is the only process in instruction that will be used enduring this course, this is what the command looks like. The target is xsl style sheet, and then the content consists of two attributes. The type attributes, which has to be text stroke xsl, and the eight ref attribute, which specifies the location off the excess lt style sheet. Let's get some practice on implement in processing instruction in zero to XML Essentials. Let's open up number eight processing instructions and here we have an XML file with an excess lt style sheet. Let's open up the XML. First of all, you'll see that it contains the root element called branches and inside that we have a child element called Branch, whose first child element is branch manager. And if I close up the first branch element by clicking on the collapse like on here, you'll see that the second Branch element also has a first child called branch manager. On what our excess lt style sheet does is to grab all of these branch manager elements and their child elements into a new output structure. It ignores everything that follows the branch manager, everything that's inside departments. It just ignores that if you'd like to have a peek at the xsl file. You'll see that in excess sanity style sheet is essentially an XML document with the roots element called xsl style sheet, and then it has a couple of template elements which dictate the output that we're gonna create. So this is the line where the bosses element is created. This is the output root element. And then he we've got an instruction to look for, although branch elements. And each time we find one do whatever these template tells us to on what the template tells us to do is to create a boss element, and inside it place all the child elements, the branch manager. So it's not place in branch Manager itself that's being replaced by the boss element, its place in the Children of the branch manager element. So that's how a style she works. And what we now want to do is to link the XML file to that style feet. I said earlier that process and instructions gonna be placed either in the pro lock or anywhere else in the document. The XML style sheet processing instruction has to go inside the prologue. Naturally, it can't proceed. The XML declaration. That has to be the first thing in the prologue. So the syntax less Stan question mark and then it closes with question mark greater than on between these two d limiters. We start with the target, which is XML hyphen style Street. And then we have our two attributes type and a dress, and these could go in any order. So time has to be text stroke xsl on an eight ref because the excess lt file is in the same folder as the XML file Weaken. Simply put, it's name put. His branches don't access. So and that's it. Let's say about changes. We've now created a link between the style sheet on this XML document. So to test that our link is working, let's go to a website which provides online xsl transformations. So, in the same folder, just open up on my transformation dot txt and copy that you are l into your favorite browser and this is a very useful website completely free for doing online transformations . So if you do end up using it quite extensively, I strongly recommend that you consider donating to the owners of the Web site so they can keep it open. Let's copy all of our XML so that all on copy. And then we paste that into the XML box and we do the same for the xsl. And the xsl goes into the box marked xsl brackets fo I went to do the transformation. You simply click on the button marked transform and insert generated fo below. At the moment, our style sheet contains no effort, and here's our output. That's just copy it into an XML document. So I'm just going to right click choose new text document and replace dot txt with dot XML . This is called it outputs. Yes, I do want to replace the file extension, and then we can just open that and paste, you know, output so we can see. Here's the close intact for our route bosses element. That's the oven intact. That man is begins. Each of our branch manager elements has created an element called Boss with the original child elements inside it. So, as I say, this is the only process in instruction that will need your in this course. But that's just a quick look at how process and instructions work and a sneak peek at what will be covering in the next section when we talk about excess. Lt style sheets 10. 02-09: XML Validation: we saw earlier that XML enables otherwise incompatible systems to exchange data. However, in order for data exchange to be successful, it's important that the integrity of an XML document could be very fight in some way and this verification is referred to as XML validation. In order to be trusted, an XML document has to pass to levels of Verification Festival. It must be what's referred to as well horned. A well formed document is simply one which adheres to the fundamental syntactical rules of XML and secondly, an XML document must be valid. The term valid is reserved for those XML documents which had here to a specific set of rules laid down for a particular type of XML document a particular XML vocabulary. The two main tools for check in that an XML document is valid, uh, be TDs and XML schema documents d t D stands for document type definition and can either be embedded within an XML document or more typically exist as an external document with a file extension. Not d. T d. DTs work well with XML documents which mainly contained textual information such as books, journals or articles like D TDs, XML schema documents allow you to define the nature and structure of your XML data, but they offer a much more robust type of validation than D TDS. Scheme is allowing to place very precise restrictions on the content of the elements within your XML documents such as the type of data which each element may contain schemers air, therefore ideal for validating very data centric XML documents where strict data typing becomes very important in the next three videos, we'll take a closer look at these three methods. Evaluated XML firstly, checking that an XML document is well formed. Then we'll move on to look at DT DS and finally will get an overview of external schemer documents. 11. 02-10: Well formedness: in order to be considered well formed an XML document as to it here to the following Seven Rules Festival, the XML document must contain a root element and elements that contains a lot the other nodes within the XML document. Let's take an example of an XML document that breaks this rule in zero to XML essentials. That's open up. Number 10 well formed this and then 01 well honed. And here we can see following the exile declaration, we have an element called day and when I collapse that element, you can see it's immediately followed by another day element. So root element repeats. It has siblings, and this therefore means that it's not a well formed XML document. Assuming that everything else is okay with the document, this is very easy to fix. We simply need to create a root element and put all our existing content inside it. So to do that, I'm going to select everything apart from the prologue, the XML declaration, my present control, Fifth and the End key and then just pressing the tab key to indented everything. And this is purely for readability. It's not in any way of requirement in XML. And now let's insert the open in tag of our root element. Let's call it days. And in the brackets environment, the closing tag goes in automatically. So I now I just need to cut the clothes in tag and pasted at the very end of the documents . And then we have it. If I now collapse our first element, you'll see that the entire document is collapsed, says this document is now. Well hold and let's just close it and say about changes. Next. We have some restrictions on the names of elements and attributes names cannot contain. Spaces cannot start with a number of special character cannot start with the letters. XML, regardless of the case on the name, must contain at least one character. It can't be completely numeric. And the game. Let's take an example of something that breaks these rules zero to, and here we can see that we have an element called course name, and it's two words, very simple breach of the rules. No space is allowed in the names of elements or attributes, so to make this well formed, you simply have to take out all these spaces, and I missed one and another. I said, Once the space Israel gun, all the names are legit and we have a well formed document. The next requirement is that element tags must be properly nested, and this refers to the situation of where you have a parent element on a child element. The tags off the child element must be completely enclosed within the tanks of the parent element. So let's have a look at examples three. Which breaks this rule. So here we have a root of notification state time when we have detail, which contains recipients on message is it's child elements on recipients, contains or other is meant to contain the two element as its child element. So we have the open intact for recipients. The open intact for two. But then we closed recipients before close into. So to correct this, we need to have to closed here. And then recipients closed here. So the opening and closing tags off the child element a completely enclosed inside the opening and closing tags of the parent element. And again, let's does save and close. Next, we have what looks like a fairly obvious one. Every Odin in tag must have imagine closing tag. And this tends to be broken by anyone who's designing XML but has more HTML experience than XML experience because in HTML five it is legitimate in some circumstances. Toe have an open in tag with no matching clothes in tag, so on to example, four and the culprits are in this paragraph, and it's basically this BR tag that's been introduced. It's taken from HTML on stands for a line break, and in HTML five. It's perfectly legitimate to write it in this way. But in XML, it's completely wrong. If it's to be treated as an empty element with no content, then you need to use the convention that we saw earlier but making it both an opening and closing tag. So, in other words, you need to put a forward slash just before the closing angle bracket, and this means that it's both an open and close intact. So it's completely legitimate if we simply copy this on paste over ALS, the illegal ones there aren't too many of them. Soldiers do it this way. Just missed one here on assumes they're all gone. That power should become a legitimate like so So this is the situation where people are designing XML and they might all into this trap. It's where they've got a lot of HTML experience that they make certain assumptions about what's permissible inside an XML document. So again, this saving close Next, we have the requirement that all attributes must have values and the game. Let's taken example of something that breaks these rules, the needs of our elements. We have three attributes. Two of them are perfectly legit, but the last one consists of a single word. So whether you take this word as the name of the attribute or as the value associating with a nameless attributes in both circumstances, it's not permitted in XML. So we would have to come up with perhaps a name for an attribute and then treat low, medium and high as the possible values for that attribute. So let's call our attribute rating so the correct syntax would be rating equals open quotes and then obviously closed quotes at the end. Save my copy this on his pasted in front of each and the strain woods and then just put the clothes in quotation mark. They told us co create control, see on dinners paste in. So having done that, we've now made all of these attributes of legitimate theater abuses called rate in on then low, medium and high have now become the values along that same attribute each time little and safe and clothes Next. We have the stipulation that all attribute values must be quoted. We can either use double quotes, all single quotes, but we must use quotes. This rule tends to be broken where people take a convention from programming languages on moving into XML. In programming languages. It's always required that you quote strings, but you normally don't need to quote any values. If something's numeric, you don't normally need to have quotes around it. But in XML, regardless of the type of data, you always place your values inside close. Let's have a look at example Number six. So here we can see for each of our invoices, we have status expected because it's textual. We placed it in quotes, but then we've made this erroneous assumption that because paid as a numeric value, that numeric value doesn't need to be in spite quotes. But in XML, it does every attributes value has to be inside quotation once again, I'm just gonna copy that and then just paste it around each of these numeric values, and that's all we need to do. So we now have a well formed XML document. And finally, we have the stipulation that illegal characters such as less than greater than 10% must all be replaced with entity references. And we've seen examples of this requirement in earlier videos. Let's look at an example we haven't encountered that's open up 07 We've seen that attribute . Values must always be quoted, but it's something you whether you use double personal single ones. If you use double quotes, then it's permissible to use single quotes inside them. But if you're using single quotes, as we are in this example, this means you can no longer have single quotes within your content. So using the apostrophe and Matthew Horror becomes illegal. Let's say that there's some technical reason for using single quotes. It makes programming in some other part of the system more convenient, so we want to continue with that. We have to make sure that we always use entity references instead of the apostrophe and the character entity for an apostrophe is ember. Send a pulse semi colon. The ones we don't meant We now have well formed XML And again that's close and save so those in the main criteria which need to be satisfied in order for a document to be considered well formed XML. However, a document that satisfies these very basic syntactical criteria isn't necessary a valid XML document. So in the next video, we'll move on to look at the criteria which have to be satisfied in order for a document to become a valid XML document. 12. 02-11: Overview Of DTDs: D T D stands for document type definition, and it's the first method of validating in XML document. The declarations, which specifying what's legal within the XML document, constitute the D t. D of these declarations they I ever be made internally within the XML document or in a separate external document when using an internal ET de. The optional standalone argument has to be included in the XML Declaration and this has to be sent to Yes, the D T. D is then placed in the prologue of the XML document above the root element, and it consists off less than an exclamation mark on the key word doc type in uppercase, followed by the name of the root element. Then we have open square brackets, the various declarations specifying the nature of elements in the nature of attributes, etcetera. And then we finish off close square brackets and a greater than sign in the case of an external D. T. D stand alone is set to know, and all we need to place in the prologue of the XML document is a dark time statement which contains the keyword system, and then the doc type statement is usually just one line less than exclamation mark. Don't type as before, the name of the root element. As before, then all we need to do is to reference the external D T D file that's usually done using the keyword system, followed by the name of the file. So in this example, we're assuming that we have a file called my D t d dot de TV in the same folder as the exile document. Inside the external DTV, we would have an XML declaration. XML version equals one, etcetera. And then we would have the D T D declarations. And these take a special torment. They're not regular XML statements. The two key elements in these declarations use the keyword element to define the nature of elements within the XML document on at least, which is short for attribute list. As the name employees, the syntax enables you to define the nature of ALS, the attributes for a particular element. Let's take a look at an example that boasts an internal and external d t d In zero. It's a maximal essentials. Let's open up 11 overview of D TDs and let's start by looking at this stand alone document that has a d t. D embedded in the prologue of the external document. That's a stuff stand alone. So here we can see on line one. We have standard early cause, Yes. And then he is the start of the dark time declaration. He was a dark time, followed by the name of the root element, which this XML document is stuff. Then we start with the definition off the ruse element staff and in parentheses. After the word stuff, we have the Children of the storm element, and in this case, there is only one child called staff member. On the plus sign after start member indicates that we can have an unlimited number of these child elements. Then we've got a definition off the staff member with his Children and there no plus lines next to any of them. But what this syntax does indicate is that these Children are required and they have to occur in the order listed in that definition. Next, we have the attribute department all staff member. And even though there's only one attributes, we still use the keyword at list. If we wanted to add a second attribute to department We could simply split this statement onto several lines so we could insert return here and then just having the readability. I would have the closing angle bracket here, and then we would just put the details off our second attribute. So let's create an attribute called Status. Character Data is the most popular data time, but another is to have a Siris of options and then placed inside parentheses and separated by the Piper character. So let's say that the let's say, for example, that we have full time claim home time and again, let's make this required. So by doing this, we have now invalidated this XML document because none of these people has that attribute. So I'm just gonna have to go inside the staff member, and it's a status equals and in quotes put either full time or part time. So I'm just gonna copy that on, paste it in for everyone. That's everyone on. Let's just leave them all this full time. That's fine. Moving back to our declarations, we then move on to the elements that actually contain data so you can see the convention, an element that contains child elements as those child elements listed inside parentheses. By contrast, elements that contain data simply have the name of the element and then inside parentheses . We specify the data type, which is normally PC data past character data. And then we have the photo element, which is followed by the keyword empty, indicating that it can't contain text in the way that first name, last name and skills Can They have PC data as their data time? And finally, we have the attributes definition for the H ref attributes of photo. So we have at least photo the name of the element that contains the attribute a treff, the name of the attributes itself, character data, the data type on required indicating that it can't be admitted. So hopefully that just gives you a flavour of what a dock type definition looks like on this is how it's embedded within an XML document. Let's just compare that with exactly the same thing, but externally. So if we open up staff that d t. D. We have the same definitions apart from the changes that I just made, but that's all we have here. We specify. It's an XML document, and then all we have is the element and Atmos definitions that we saw before. So to make this current, I'm just gonna go into stand alone and just copy the new version off these definitions and then just replace the originals. It's close and safe. That's finished by a very fine that the document that we have is valid as defined by our dog time definition. So we'll copy everything and let's just across route browser Visit xml validation dot com and we just paste in our code and from value dates. No Harris have been found. Let's just go back to the XML document and introduce an error. So now at least we added this extra attributes called status and it's required. So we release the status for any of these stuff. Remember elements we have now invalidated our rules. So when we click on Vanya late this time, you can see that we have an era. The attributes status is required that must be specified for the element Time start member 13. 02-12: Overview of XML Schemas: in this video we'll move on to look at the second method of validating XML data which is to use a schema definition document XML schema of far more powerful in D TDs. And this could become a very complex topic. So we'll be doing is simply having a very brief look at some of the key aspect off a schema document. This is what the skeleton of a schema document looks like. XML schema documents are always external. Unlike de TDs, there isn't an option to embed the definition within the XML file itself. And unlike De TDs, XML schema documents don't have a special definition language. An XML schema document consists of standard XML. This is the skeleton of any XML schema document. Like all XML documents, there has to be a root element on that route. Element is schema to avoid possible conflicts with any other XML vocabularies, XML schemers use the name space. So here we have a name space definition which points to the XML schema definition and specifies in this case that we use in the prefix X s And then whenever we use any elements within the scheme of vocabulary, we prefix the elements with excess colon. So as you can see, excess cool on schemer is the root element. And then we'll have a series of definitions within that route element, all using the same prefix excess. The Lincoln XML document to an external schemer document the attribute. No name space schema location is used to specify a location off. That document on this attribute is added to the route element. However, the attributes itself is within a given name space. Hence it's preceded with another name. Space definition. So here is pointing to the schemer instance definition on we specifying that we're going to use the prefix excess I once we've made that name Space Declaration. Then we can say excess. I know names based scheme a location equals and specify the location of the scheme a document then within the rules element, we have our XML content, and that content now has to it here to the rules laid out in our scheme. A document in this case root dot access D. So if we apply this principle to the staff document that we've seen a few times in these videos, the root element is called staff. So it's here that we insert our excess I know names based scheme a location attribute on. We specify that staffed on Excess D is the external schemer document to be used for validating this XML document. So here we have a diagram of our simple staff. Example on. Let's look at some of the key aspect off a schema definition for this document. First of all, the distinction between complex times, Andi simple types within a schemer document. Any element that contains other elements is defined as a complex time. So in our staff, document staff contains one or more staff member elements. Hence, it's a complex type and staff member contains four child elements, so it's also a complex time. In addition, the photo element, which, if you remember, contains no text but has an attributes is its child is also treated in a schemer document as a complex type rather than a simple type. So simple times are either elements that contain text or data or attributes, which by definition always contain a valued on. The other aspect off schema definition, which will have a look at, is the use of indicators. So now stuff example. We look at the use of an occurrence indicator to specify that the staff member element can occur one or more times. And we'll also look at the use of an order indicator to specify the order in which the child elements of the staff member element must occur. So now training folder in zero to XML Essentials. Let's open up some folder 12 and let's begin by opening up the XML stop the next time L and in our route element that's the stuff. We can see the use off the no name space schema location attributes on. The value of that attribute is staffed on XSD, which implies that we have a scheme, a document called Stuff that access T in the same folder as the current XML document. And now let's switch across to that document. And as you can see, we have a regular XML document which has a root element called schemer. When that element is in the schema definition name space which, according to the name Space Declaration, is using the prefix X s. Hence the schema element uses that prefix excess colon schemer as a postage, A schemer. Now let's look at our complex Andi simple types so we'll start with complex types. And if you remember there two occasions when you have to use complex types, the first is for those elements that contain child elements. So we start with staff, the root element and inside it we have a complex time definition, and inside that we can see the use or order indicator and in this case, the order indicators sequence. It doesn't really matter because there is only one child element, so it's a sequence of one element, basically. Then we use excess elements to define bad element on. We've also got on a currents indicator. Max occurs, equaled unbounded, which means that we can have a Zeman any occurrences off the star pepper element within staff as we want and you can see that we've got a Mexico's. We haven't got a minute. CA's by default, the minimum occurrences one. So simply by omitting it, we're saying that the staff member element can occur as many times as we want, but it must occur at least once, which is the default. So the use of ref implies that we have a definition all staff member on here. It is so xsl elements, staff member on because it contains child elements. We don't have excess complex type. And again we have the use of the order indicator sequence implying that the four child elements have to occur in the specified order. So that's how complex type one. Before we move on to simple types, let's find complex type two. So if you remember the second occasion when you need to use the complex time definition is where you have an element that has attributes at its child on, that's the case with the photo element. There are no child elements, but there is an attribute. So excess element photo. Then we've got a complex type definition, no elements, and then inside it we have the definition of our attributes, which takes us onto the definition off simple types. So you concede it on attribute is a simple time, and to define and attributes as a simple type, we simply specify its name and the data type and for an attribute. We then specify that its use is obligatory by specifying use equals required, and we've got the same definition here for the department. The type of string on the use is required then, at the top. We have the definition of our element. Simple types, excess element, they medical skills type equals excess string. So all three of our symbol types have string as the data time, and one of the great strength of schema definitions is that it allows very robust type definitions. And it also allows you to specify your own custom data types. So that sound very brief. Look at schema documents. And also that's the end of our very brief look at the way that XML works in the next section will move on to look at how Excel handles XML documents and XML data. 14. 03-01: Importing XML into Excel: in this section will move on to look at importing XML into Microsoft Excel worksheets. So we now working in 03 XL the next time l and let's begin by going into number one important XML into Excel. Yes, the XML file that we are going to be important. So let's begin by having a look at the structure of the file, you'll see that it contains a root element called courses and then inside this route element, we have a repeated element called course, and the child elements off the course element contained the actual data that we want to import. This simple three level structure is the ideal format that you need whenever you want to import XML into Microsoft Excel. So obviously you have the obligatory root element, and then you have a repeated element, and each of these repetitions will become a road within an Excel table, and the column headings of the table are provided by these troubled elements. So each of these repeated elements has exactly the same child elements in the same order, and that's very important. If the schemer permitted different child elements for each of the repeating course elements , then the important to excel wouldn't be successful. It's very important that the scheme a document defines the child of the repeated elements as the sequence. In other words, each of these child elements must occur, and they must occur in a particular order. Let's have a look at the schema document. How that specified. So let's open courses that access D. So here we have the definition off the root element courses on because it contains other elements. It's defined as a complex time, and it's child element is defined as a sequence. So it's a sequence of one child element, which can repeat as many times as necessary. And here's the child element cause. This is one that repeats and these repetitions air specified. Using the max occurs attribute, which is set to unbounded the men occurs, attributes defaults toe one, which means effectively that the course element must occur at least ones, but it can occur an unlimited number of times, and then the course element is itself a complex type because it contains child elements. These child elements are sequence and you'll notice that none of them has a max occurs. So the default for of men occurs and Mexico's is one. So this means if you admit both Max and Mina occurs, each of these elements must occur once and only ones, and because they are part of a sequence, they must occur in the order specified within the scheme of document. So that's the structure will be imported. Let's now have a look at the process. So number one important XML into excel that's open up courses of XLs X. And here we've got a workbook that contains a single worksheet, and we want to import that XML as a table within that worksheet excels. XML commands are located on the developer tab, so the first thing you need to do is to make sure you have the development and visible by going to file options and then clicking on the customized ribbon category on the right hand side of your screen. You'll then see all the taps that are visible and available on to make developer visible. You simply click in the track box next to his name. When you click OK, you'll find that you got a new tamp, and when we go across to that town, we have an XML group with all the XML commands. So here we would click on import and then, if necessary, just navigate to the training folder and into 03 excellent XML and then number one important XML into excel. And then we simply double click to import the XML file on. We obviously have a worksheet ready for the data were important. So we import XML table into existed mark treat ment of specifying that the schemer document should be used to verify and validate the XML. We click on properties and here we switch on validated data against Schemer that important export the properties that we're looking at our or a map and XML map, which is the mechanism that excel uses to determine which part of an XML file corresponds to which column within an excel table. I will discuss XML Memphis in the next video. So for the moment, let's just click OK, and then when we click OK, again, Excel goes ahead and imports all of the data. And it's very easy to see the correspondence between the data that we've imported on the original XML. They've been back to our school schedule. Here's the first row basically, So we can see that the course name is advanced Web design. We have a start date of the eighth off Jan and duration of war in the price of 900 on all of these things weaken see, replicated within the imported data. The data is housed in an X old table. So we could if we want to rename that data, and we can also format the data within the table in any way, that's convenient. So, for example, the start date is held in the standard XML date time format. We wanted to look a bit more familiar. We could just change it to the Standard Excel short date format. And, of course, if we were to export this data from Excel, Excel would be intelligent enough to convert it back into the standard XML daytime format. The relationship between the XML File and the Excel table that we're looking at. It's examined by this thing called an XML map. So the development tab if you click on the source, this displays the XML map, and as I click on each of the individual elements, we can see the correspondence between the various elements within the XML file and the columns within the table. So in the next video, we'll talk a bit more about XML men and how they relate to the tables that house your imported XML. 15. 03-02: Creating an XML Map: in this video will discuss the role of XML maps in excels XML Work flows. We're working in 03 XL and XML. That's now going to number two creating an XML map. And to begin with, let's open up courses that XLs X on work with the XML file that we worked with in the last video and what we saw in the last video is that when you import an XML file, Excel has to create an XML map in order to import the data and know which part of the external structure goes into which column within the Excel table that it creates. So now we'll move on to look at creating an XML map manually before you import any XML data . So to work with XML is restored. In the last video, we moved to the developer Tab on. On the right, we have the XML group and to work with external maps. We click on the source button, which makes the XML source task pain visible. To create a map, manually click on XML maps and then click Add, they told the three sub folded to You'll notice that both XML and excess defiles are displayed. So Xsd is the schemer document and obviously XML is the XML document and you convey build your map from either of these two documents and providing that there are no errors in the XML file. You should get exactly the same result regardless of which of the two use. So let's use this scheme a document for the Courses XML file that we saw in the last video . Excel generates the man and you have the option of renaming the map and you'll notice that the default consists of the original file name, followed by Underscore map. Let's rename it cause schedule and then click. OK, so within the XML source task vein, we can now explore the structure of the XML document. If we want to do exactly what we did in the previous video, which is to import the XML file as is, we can simply drag all of the elements which will generate the column headings by dragon, their container. So they're all contained in the course element. We simply drag the cause element into a one, and then we have a table which corresponds to the XML elements on which the column headings are based Let's Undo. But the real benefit of this workflow is that you can choose exactly which elements you want and exactly where you'd like to display them. So let's say that the first thing we wanted is playing out. Table is the start date, so I drag start dating to 81 Then let's say we want the course name, but we don't need the category. And next we want to move on display for all of the tutor information. So I click on the first tutor element and then hold down control toe. Have the other four, and with them selected by then drag into C one. Next, let's have duration in price again, control clean to select the second element, and then we contract them in. And finally, let's have the three men you feels so you'll notice that everything that has Bean added to the table is in bold. And in fact, the only elements I didn't add was category on that stand in plain. When I click on an element name, Excel highlights the corresponding column of the table. When I click on any column within the table, it highlights the corresponding element name in the XML source task pain. So using this workflow, I've been able to customize which fields I want to display on the order in which I want them displayed in my example table. And of course, now, when I import the XML file, it's obviously going to follow the map pins that I've specified. So we go across the developer, clip on import and now bring in our school schedule. As we can expect, you'll see that Excel respects the column order that I've specified and just places the XML into the correct column. So let's close this file and save our changes and let's look at a second workflow. So in the same folder, we have a schemer document called Expenses That Access T. And let's say this time we want to use the schema document purely to create an XML structure, which we can then manually populate within excel. So let's open the Excel file that we're going to use cold staff data and then let's create the external map. So we click on XML maps people had, and this time we want expenses that xsd and just to remind you that we can let's rename the map Let's call it expense claims don't like. Okay, so here we're looking at the structure that we need. So is the very simple three level structure that we've discussed. We have the root element called expenses, and inside that we have. What we can assume is the repeat in element called expense on the child. Elements of expense are meant to contain the actual data, so they will become our column headings. And as before, we can generate a table simply by tracking all of the fields into a one on. Then we've got them in exactly the same order as the originals. Let's undo. Let's do a bit of customization, so we'll have the person's name first. Then we'll have a date. Let's have a date reimbursed next. Then let's have their brunch, the category under which the expense claim fools And finally, the amounts so amount claimed and amount reinvest on. What we can then do is to complete the table by inserting a calculated column. So let's say we'd like to keep track of the percentage that was reimbursed. So this part of our table is static and it lives in excel only because there's no corresponding XML element. When we export this data, the percentage reimbursed will not be exported with the XML. So we can now enter data knowing that it's going to conform to the schemer off our proposed XML documents on at any point in the future, we could either import XML into this structure or we could enter data into the structure within excel and then export that data as XML. 16. 03-03: Importing XML Data Incrementally: So far, we've only discussed import in a single XML file. But you may encounter work flows where you have several XML files that need to be combined into one data set or where the XML is supplied to you in batches. So you have to import the data incrementally. Let's look at those two scenarios. So that's open sub folder three important XML data incrementally. And then let's have not the Excel file cool schedule. So we started from scratch. There's no XML map on There's no XML data in the workbook at this point in time, and what we want to do is to combine all four of these XML files into a single excel table . Let's look first of all of the technique which won't work, so you can then dismiss that technique and not waste time trying it. We go to developer on import. We can simply select all of these files and click on import. But this generates an error because Excel doesn't give us the option of creating the XML map that we need so we can click OK to dismiss the error message on what we need to do when we're important. Multiple XML files is to specify the XML map in in advance. We saw how to do that in the last video. Best of all, we bring up the XML source task pain. Then we click on XML maps and add, and wherever possible, I tend to use thes schema document rather than the XML file. Let's click on open except the default name and click OK. And let's say this time we're happy to have everything in the same order is the XML so it can simply drag the course element onto the page to generate out table. And now, with this mapping in place, we can go ahead and import or of those exile files. We select the full files that's like on import and we know imported the schedule for or quarters starting from January, right up until December. And naturally, this has only worked successfully because the four XML files that we've imported are all based on the same schemer and can therefore all use the same XML source map that we've defined. So that scenario one let's close them without saving our changes. And then there's reopened the file and look at a second similar scenario this time, let's say that we'll import the data incrementally as each quarter becomes available. So let's say that when we do the first import, only quarter one is available. So this time, because we're importing a single XML file, we know that Excel will automatically generate the XML that forests so we can simply click on import and that's important to you. One on will leave the default XML table inexistent worksheets and click OK, Excel brings it'll the data and it automatically generates the XML source, man. Now it's a few weeks later, we've got the quarter to data and we want to add it to our existing quarter one data. So as you can see, this ends on the 31st of March and we want to add the course information for April, May and June the second quarter. So the key thing here is when we go to developer, we need to sell properties. So we click on map properties and at the bottom, we've got the option when refreshing or important, data override or upend. And of course, what we want to do is to append new data to exist in XML tables. So they're like OK, and now it's safe to import Q two. So here we've got the last quarter one and the first quarter two on. We can then just continue with that. At a later stage, we import Q three. Herman. Finally, we import cute for I'm would be finished. We've got all the information right up to the end of December. So those are the two main scenarios involving the important or multiple XML files. Scenario one, importing them into the same table and then scenario to incrementally important them into the same table. 17. 03-04: Validating XML Data: in this video, we'll look at validated XML data as you import it into an Excel worksheet. So 03 Exelon XML. It's now going to number four, validating XML data. Let's begin by opening the scheme a document that will be working with courses, not XSD. So here you'll probably recognize the three levels which an XML document needs to have in order to be successfully imported into Microsoft Excel. First of all, we have courses, which is the root element. Then we have the repeated element course, and inside that we have the child elements, which will correspond to the columns within an XML table in Microsoft Excel and these child elements the specified to be a sequence. This means that they must occur in the specified order, and in this case, very importantly, they can only occur once. None of them has Mexico's on bounded or anything which detracts from the default of me knickers once and Max occurs. Once you know the words, they must occur once and only once, so to test excels powers of validation. Let's just open up the XML file and break the rules slightly. Let's say that one of the tubes that e mails is missing. If we simply leave to you to email intact and then delete the email for that tutor, we haven't broken any rules because the element to to email still exists. But if for some reason the system that generates the XML completely removes Tuesday email for that record that we have now broken the rules, we've broken the sequence that specified in the scheme a document. So let's save out changes and so that we don't love the file. It's also closed most the scheme and documents on the XML file. Now let's go into excel so we'll open courses that XLs X and then import. The file to do is we go across the developer and click on Import. We're in the right folder, 30 days in XML data, and it's double click on the XML file. In order to ensure that Excel value dates using the XML Schema document, we now need to click on properties and activate the track box, validate data against schema. And now when we click OK and OK again as we anticipated, we get an error, failed scheme, validation, and when we click on details, Excel explains exactly what it doesn't like on the two most useful pieces of information it's supplying are the reason element to to day rate is unexpected. So in other words, it's sane in record one, we're going straight from tutto last name to to to day rate to to email. Is there four missing, which is also confirmed in the expecting attributes. What you to email is specified is being the missing element. We've engineered this era, but hopefully this demonstrates that Excel will pick up errors if you ask it to validate using a schemer document. So whenever possible, If you're working with a client who supply an XML, you should ask them to also supply a schemer document which you can use to validate the XML . 18. 03-05: Multilevel Import Multiple Tables: in this video what we want to look at working with an XML document, which has a slightly more complex structure than the one tweet clean so far. Zero Free Excel in XML that's now going to number five multilevel import on multiple tables . Let's have a look at the XML file will be working with its cool branches, and it contains information about people working at a given organization. So we have the branches root element. And then we have a repeat in Branch Element inside that we have branch manager, and then we have the actual information. So these are the elements which will correspond to the columns within an acceptable table within Microsoft Excel. So far, so good, however, only branch managers a listed at this particular level within the hierarchy. To get to the heads of departments, for example, we have to drill down into the department's element and the repeating department element. And then within each department element we have a staff element on the first child. Element of staff is head, and here's where we'll find information about all our heads of department. Then, after the listing for the head, we have a star remember element, which again has exactly the same child elements. But the thing that makes this situation selectable complex is that the child elements may be the same, but the parent element is not so. We have stumped member, we have head, and we have Bronze Manager. All that's slightly different levels within the XML structure that's just close this file. Then let's just have a look at the diagram off the XML structure. So if we have a broad interest or PdF and if you're using Adobe Acrobat, you can type control elf to go into full screen mode and then control one to go to actual size. And then let's just move. It's absolutely so we can see the whole thing so we can see that the elements that all contain text exist at different levels within the structure. So we want the trial elements of Bronze Manager, head and staff member and within Excel. The only real way of doing this it's toe have three separate tables to bring the branch managers into one table, the heads of departments into a second table and the staff members into assert. So let's press escape and just close the branches. A Pdf file. So now let's open up the Excel file staff data and inside you'll see that we've got a worksheet for each of the three bits of data that will be bringing in staff department heads and branch managers. So the first thing we need to do is to create three XML maps, one but each of the sections of data that we want to target and as we've seen before to do that, we go to developer click on the source button. This brings up the XML source task pain, and here we click on XML Maps. Then we click on Add on its work Out waiting to fold it five multilevel import multiple tables and then let's base the map on the schema. Document branches don't xsd. Since what we create in three XML maps from the same schemer will need to rename There's gonna be stuff. Let me have a second. That's cool. This one heads remember, third manages I mean, we click OK and here l three XML maps. Let's start with staff. So here we need to navigate to the staff member parent element and we want a little bit child elements of START member. With the exception of voted, let me just remind you why we don't need photo. If we open up the XML document branches dot xml, you'll see that photo is what's called an empty element. In other words, it contains no textual data. What it does contain, however, is the attributes called a dress on. This contains the location off this person's photo. So what we need to do is to control, click on a photo, deactivated and then control click on age ref to activate that, because it's a draft that actually contains the data that we want. With that selection in place, we can then create the XML table. I drank in the selection into the Work Street, and it's finished by renaming age ref photo. And then we simply need to do the same thing all department heads and branch managers. So that's navigate up to the head section. The highlight the Parents, which is an highlights. All the trial elements control linked to de select photo and control glimpses selected a dress. Let me track this election into the work feet and again, let's rename age ref and finally, branch managers who are at the top. So those three map pins in place, we can now import our XML file. And it's that one XML file that will populate along the tables very often. When you've got these mapping set up all point into the same XML file. Import in the XML file will populate all the tables in one hit. But even if it doesn't, you simply select another table and reimport the same XML file said. To import the XML, we go to developer on import and double click on branches dot xml. So here we have a lot of bronze managers and our department heads and allow staff members. So that's an example of how we can populate multiple XML tables from one XML file. That same out changes. And in the next video, we'll discuss strategies for bringing in data from her multi level XML file into a single XML table. 19. 03-06: Multilevel Import Single Table: In the last video, we looked at important data from different parts of a multilevel XML file into separate tables. In this video, we'll look at doing the same thing but assembling all the data into a single table. So 03 Excel in XML. Let's open up some folder six multilevel import single table. So let's begin by opening up. Start data Unified and here you'll see we've got exactly the same file that created in the last video. So we have three separate XML maps, heads, managers and stuff and then is restored in the last video. We'd use these three maps to populate three separate tables so, naturally, what we can now go on to do if we want to have ALS, the data in a single table is to manually create a new table and copy and paste all of the information into that table. The limitation, of course, is that the data in the table will be static. It won't be linked to the XML in any way, so let's close the XML source task pain and then let's just make the profile column a bit narrower so we can fit everything onto one screen So I'm going to select all three seats and then just resize profile like so And then let's de select our sheets on group seats. So before we create our unified table, we need to identify the role, the job role of each of these individuals. So I'm currently in branch managers, so I'll just add a column here called Role and just Time Brahms manager and then copy that down into all of the other Rose. Then I'll do the same for department heads and staff members. So now we've got a column to specify the role of all of the individuals. We can create another table. I'll just move that to the end and just call it full stuff. And we vendors copy all of the data across total staff. So the first copy, I'm gonna take all of the data, including the headings and then for the other two. I'll obviously just need the data. I won't want to copy the headings. Seven department heads. I click in the first column first row of data and then used the usual keyboard shortcut, which he probably no control shift end to select all the way to the bottom and finally we do the same to stuff controls it and on a copy and paste. So this is one approach. We import all the data into separate tables so that we can access different sections of the XML file, and we then manually assemble all the data into a single table on. As I said, the limitation is that this table is in no way dynamic. I now have all the data in one table, but it's not dynamically linked to any XML source, and it doesn't correspond to the structure off the original XML. So if I wanted to export this data back into the original XML, I wouldn't be able to do so. So let's now save out changes, closed the file. And now let's look in our second example. So let's open up stuff. Data amplified, and what we'll do in the second example is to create a single table, which references the three different job roles that we've just seen staff members, heads of departments on branch managers. So let's go across the developer and creates an XML man. To do that, we take a source and then in the XML source task pain. We click on XML maps, then add and elects the same file. I'm going to switch across to hold a six multilevel import single table a man DoubleClick on branches down xsd to generate the map. There will only be one map this time, so there's no rolling to rename it. So let's just like Okay, so let's start by putting in all of the staff member data. So as before, I'm going to control click on photo and then control. Click on age rest, drag the selective elements to create the external table and then rename a dress photo. So what we can now do is do an in the details of the heads of departments and branch managers in the context off each staff member. So let's begin by specifying the department and the branch at which eat start member works say we scroll up and we've got the department name so we drag in name and we'll need to rename the column. Let's just call it department and then we want the name of the branch again. The element is called name and we can just read a mitt brunch, and now we can add in the name off the head of department on the branch. I said Before I do that, I'm going to switch on word Ram. Just get back to home Special lead, right then vertically aligned to the top. Let's also get rid of all these Underscores. So while that selections in place is tight control age which is a short cup, the replace and then we're replacing under school with a space and replace all. So now we can start with the name off the department head head section we track first name and the same for last name. And finally you do the same for the branch manager. - So complete sound mapping. And finally we import our XML data into that structure. So developer, import and branches. So as we look at each stunk member, we can see the name of their head of department on their branch manager. So the staff member details occur once and obviously the heads of departments on branch manager details are repeated as many times as necessary. So hopefully this gives you an idea of how you might use data from different parts of a multi level XML structure within the same table 20. 03-07: Exporting XML Data: In this video, we'll look at exporting XML data from an Excel workbook. So 03 Excel in XML. Let's now go into the last sub folder, seven Exporting XML Data and then open up the Excel workbook Expenses Q one and Q two. And here we've got a single work feet that contains its, um, Excel data. It's not an Excel table, and the first thing to note is that we can't simply export any old Excel data as XML. And the reason for this is that unlike formats such as CS, V, XML is not generic. Whenever you work with XML, you work with a specific XML vocabulary. In other words, you're XML Document adheres to a particular structure normally defined by a schemer, Document said. We go across the developer, you'll notice that the export command is unavailable. So before we can export XML, we have to link this particular data with an XML vocabulary. And as we've seen in previous videos, the way that Excel does. This is through the creation of XML maps. So we make the XML source task pain visible by clicking on the source button and then at the bottom, we click on XML maps. We keep on ad, and now we need to navigate into full the number seven. And here's our excess defile our schemer document. We double click on this and when we click OK, we now have an exceptional man, which contains all of the elements defined by the schema. We can see that this external vocabulary has the simple three level structure, which excel requires. We have the root element expenses, the repeated element expense and the Children of the repeated element are the elements that contain text and which will correspond to the columns with a now Excel data. You'll see we've got a match or all of the columns with the exception off percentage reimbursed on. This is obviously a simple calculation, which we've done in Excel. Let's say we need it in Excel frowned convenience. But because it's not part of the external vocabulary, we can't export it. So when we do our mapping, we simply need to miss out percentage reimbursed. So the 1st 5 columns are all in the same order name branch bait claimed amount claimed and amount reimbursed. I could just use the control key to select all of them and then dragged the selection onto the first column. And because they're all in the same order, there automatically mapped in the correct fashion. Then I want to miss out presented, reimbursed and Man Date reimbursed on category, so I couldn't click and control click to select them. But what I can't then do is to simply drag this election onto Dane reimbursed. If I do that, I'll end up with two separate tables. Let me show you says I drank here because I'm missing out a column. I've now got Table two and Table one, which, of course, is not what I want, so I just need to undo that. So what we need to do is to make sure that the scented reimbursed it's part of the table before we'd met the last two columns on. We can do that simply by going to the bottom and drank in the resize handle so that column F presented Reimbursed is now part of the table. So now it's safe to select the last two columns. Dave reimbursed category and dragged him onto column G to complete the mapping. So now we're going to table tools design. You'll see we only have a single table, and while we're here, let's rename it so without having in place, we can now safely export the data as I click on each column you can see in the XML source task pain the corresponding element. The only exception is that when I click on percentage reimbursed, there's no change within the XML source task pain Because that's the only column. It's not mapped to element with that XML mapping in place when we go to developer export is now available on the interest rate on exports. Unless just call the file expenses on export. And here's the exploded final expenses, Toe XML, and when we open it, we can see that it corresponds to the exile structure that we saw in the XML source task pain, the columns and now Matt correctly to the appropriate elements. And as we anticipated, percentage reimbursed has not been exported because it's not part off the external structure. So that's all there is to export in XML from an Excel worksheet and that completes our look at importing and exporting XML in the next section will live on to look at two very useful Excel functions, Web service and filter XML 21. 04-01: Using the Webservice Function: Microsoft excels. Web service function is used to get an XML response from a Web service, a Web service. It's an application designed to deliver a particular type of information to any client who requested when the request is made, parameter values and normally supply, which determine the result generated. It's very similar to simply visit in a Web U R L and having a Web application which supplies content to you. The big difference is that a Web service is designed to supply content not to humans, but to another application. So in this case, that other application is excel on. The mechanism that we're using is the Web service function. We specify the Earl of the Web service application, and normally that girl has embedded in it one or more parameters on the Web service as a result of that request, produces an XML response and then delivers that into the cell, which contains the Web service function. Web services can deliver other types of content apart from XML. But in the case of Microsoft Excel, XML is the only response which is understood. So in using excels Web service function, you can only work with Web services capable of producing an XML response. So let's take an example in the training folder will now working in zero for Web service filled exile exposed. And that's going to fold a one using the Web service function. Now let's examine our example international pricing that safe in that and in this example, we have a series of prices using G B P as the base currency. We want to be able to translate those prices into all of these different currencies, using up to date conversion rates. So what we want to do with the Web service function is to find a Web service which will give us the conversion rate from GBP. $2 euros, Canadian dollars, etcetera. So let's now return to the training folder. And if you open up the text vocal Morneau software for this demonstration, we're going to use Mondor Torborg as our Web service supplier. And the reason for doing this is that they have a 14 day triable service, which allows you to make a limited number of requests to their Web service. So the first thing more me to do is to sign up, so just copy this sign up, you are l and paste it into your browser using paste and go and then just fill out the information and click on request demo key now. And if all is well, within a few minutes you should receive an email like this one, which contains an A p I key. So I'm just gonna call me, They say p i and paste it into note pad. So now we just need to know the u r l that we need to access when we use in excels Web service function. So we go across to the website, click on the link on the left here, currency exchange rates, XML Web service and then just click on this web ap I access link on the first. You are well, that we see here gives us all the information that we need, and it gives us a breakdown of the six components to that You are l on. All we have to remember basically is that the base currency comes first. So in our example, obviously the base currency will be UK price. Then comes the target currency, and then components five and six. You'll notice a both optional. So in our case, it will simply omit them. The amount of convert devolves toe one and the date of conversion defaults to the current date. So that's exactly what we want. So we can just copy the first part of the URL admit in the last two sections. I'll just face that here as well and save my changes. So now we're ready to use the Web service function in Excel. Let's start with USD. I have you are else still on the clipboard, so I can just tie equals Web service and the girl is a string. So it has to go in double quotes, double quotes, and I can just paste it in close quotes, close brackets presente. And of course, the thing that I forgot to do was to put in my keys. I left this sort of default key in there. I obviously need to replace that with the A p I key that they supply to me. So let's just go back on, Grab that, just paste it in here now we should be good to go and we have a price. Let me just widen this cell so that we can see the result on then see what we need to do before we can actually use this value so we can see that in this case, the Web service actually returns the value that we want. The only problem is that the value is quoted, so all we need to do to get out calculations to work is to remove the quotation marks on. We can do that using excels, substitute function. So around our Web service, let's put the substitute function and use that to remove the double quotes so equal substitute. The Web service function is the first argument. In other words, the text. We put a comma at the end. Now we supply the old text. In other words, the text we want to remove on that text has to go in double quotes. Which raises the question. How do you put double quotes inside double quotes? I don't know if you've ever had to do this, but basically you double up. So you put two double quotes to represent double close. Hence we put open quotation marks to double quotes, close quotation marks. So four double quotes in total comma. What do we want to replace them with? Nothing. So we simply put open and close double quotes to indicate the mold stream close brackets. And, as you can see once the quotation marks have gone even though, strictly speaking, we still have a text value because the text value consists of numbers only Excel automatically converts it to a numeric value and uses it to perform the calculation. So I'm just going to reinstate the narrow column with so you can see all the different currency rates. And then the final thing we need to do is to modify the default formula. So it's not using neuro and us deep, but it's using G V P as the source and then picking up the conversion currency from the cell above the cell containing the formula. Simple way of doing that is to festival, delete euro and replace it with GBP. Then we delete USD. But this time we need to replace us D with the contents of this cell above the cell containing the formula. So to do that, we close quotation marks, then put an empress end but can capture nation and click on the cell above which in this case is J two, so that picks up the currency in that cell. Then we put another empress end and then opening quotation marks. So you just insert in that variable string into the URL. So when we presenter calculation should now actually be the correct one, what we now do is just to copy that formula to the rights and because there are no dollar signs were always picking up whatever currency is in the cell above the cell that contains out formula. So that's an example of using excels Web service function. And on this occasion, the result that was produced was, in fact, the result that we needed. In most cases, however, the Web service function returns an XML document containing a hierarchy of elements and attributes, and you then need to use the second function, filter XML to navigate within that structure and extract the data that you need. So in the next video, we'll move on to look at working with the Filter XML function 22. 04-02: XPath Overview: in the last video we used excels Web service function to retrieve information from the Web service and the information that we retrieved was exactly the information that we wanted. This is not normally the case. What's normally returned by the Web service is a complete XML document, and you then need to navigate your way through this XML document to find the data that you actually want. The function that enables you to do this is filter XML, and it takes two parameters. The first is the XML that you want to work with, and this is normally supplied by using the Web service function. The second parameter filled XML is an expert statement. So what is expert expert is an XML technology, which is used to retrieve information from within an example structure in the expanse. Statements used with filled XML are what unknown is location steps, So a typical experts statement will consist of one or more location steps separated by the forward slash character. The use of location steps in experts is very similar to working with a directory structure he started on the C drive and you going to fold a one inside folder one you find folder to and so forth. So in this illustration, we're working with the branches. But XML structure that we saw before and we use in three locations steps the first take sister branches, which is the root element. The second takes us to the child element of branches, which is branch, and the third takes us to a child of the branch element, which is branch manager. There are three parts to a location step statement festival. You specify the access that you want to travel along, and there are quite a few to choose from the default. Is the child access. In other words, you start with a parent and you look inside for one of the child elements. The second component is called a no test, and this allows you to specify the node or nodes that you want to retrieve. Traveling along the specified access. The third part of a location step is optional. It's called a predicate, and it allows you to filter out any knows that you don't need A from the note seven you've retrieved by traveling along a given access and looking for a particular set of nos so it returns while branches. Example. We have three locations. Steps, branches, branch, branch manager in locations. Step one. We travel along the child access to get to the branches element in location. Step two, we again travel along the child axes, and this time our no test is branch and then in location, Step three were again traveling along the child access and this time the no test, its branch manager. And, of course, in none of the three location steps, do we use a predicate? As I said, the predicated is optional on. We will encounter its use later. So just to recap, location steps are separated by the forward slash character. Whenever you see a location step, which appears to consist purely of a no test, this means that the child access is being used because it's the default. You don't actually need to specify the child access you could if you wanted to, and this is how it would be written child fathered by two Coghlan's branches child branch child branch manager. But of course, because it could be omitted, everyone admits it so it's just written branches branch branch manager. So that's a quick overview off X Path location steps in the next video, we'll look at the use of expert statements in conjunction with the Filter XML function and then in the next section, when we discussed the use of xsl style sheets, will see how expert is used to enable you to home in on the various parts of an XML structure as you're performing transformations. 23. 04-03: Using FILTERXML and ENCODEURL: in this video, we'll move on to look at the other two functions which are available when you're working with XML in Excel, Filter XML and encode U R L So in zero fall Web service Filter XML the next part that's now go to number three years in built XML and encode u R l To see how these two functions work , we're going to be using a Web service from a free website called Geo Names Torborg. So let's begin by opening up geo names dot txt And here we've got all the information that we need to work with this pre site, and the first thing that you'll need to do is to sign up for a free account. And here's the U. R L at which you can do this. So let's just copy that you are l. Then just move into your favorite browser and use the paste and go feature to go to that you are l. So the first time you visit the website, we'll need to create a new user account. Just choose a user name those faces allowed, and then just put your email, confirm your email, choose a password and confirm your password and then go ahead and create an account on the confirmation. Email will be sent through to the email address that you supplied. The email would look something like this, and it will include an activation link so you simply click on the activation link to activate your account. Once he validated, your account will automatically be logged in and thereafter. Future. You obviously can just click on the log in button, put in your credentials and log in. And then there's just one final step you need to perform on. That is to going to manage account, and you get to that by clicking on your user name in the top. Right. You'll see you've got the two wings there, one for logging up on the other, which consists off. Your user name is the managed account link. So you click on that, and here you have the opportunity to change your user email and to change your password. The thing that's most important for our purposes is to be able to use the free Web services , and to do that, you have to click on this link at the bottom, says he can see it says the account is not yet enabled to use three Web services. Click here to enable, So we click on that and then we good to go. So from now on, we can use that free Web services. There is a limit as to how many requests you can send that day, but beyond that, it's all free. So we now click on Website This a Van Web service overview. These are all the different Web services. And of course, the XML column is the one that we're interested in. Because Excel has an offer an option to use Jason, it only uses XML using the Web service function that we've seen. So the Web service that we're interested in using it's postal code search, which is you can see is available in both XML and Jason. So let's click on that. And here we have all the possible parameters that you can use with this Web service, and then we go to the bottom. We confined an example which we can use in Excel with the Web service function. So let's just copy that, and I'll add that to the text file. And, of course, when using this girl you simply need to replace the user name demo with the user name that you chose when you signed up to this service. And, of course, the other thing will need to change is this. We want to have different postal codes when we actually use this facility. So let's leave the text file. It's no open up the Excel file. We're working with postal codes. Example, and inside it you'll find hotel information. So we've got the address of each hotel and then the rating, the average rating given by our start members. And let's say to completely solution, we need tohave the latitude and longitude off each of these hotels, and we're going to use a Web service. To obtain this information, select three new columns. The first will contain the XML that we retreat from the Web service. Then we want the latitude, longitude, and it's coming full minutes. The logic of having a separate column for the XML is that both the latitude and longitude can then be extracted from that one cell instead of embedding to Web service calls in each of these two columns, and this way of working becomes quite important. If you're paying for every single call that you make to a given Web service. So now let's put the Web service function equals Web service. And as he saw in the previous video, the only parameter is the URL, which is a string and which we can just can't be from our text file. And before we do that, I'm just gonna change the maximum number of rows toe one. We don't really need that much detail since we're only interested in the latitude and longitude. So we called you all of that on paste it in and then we need to make a couple of changes. Festival the postal code that we're looking up is the postal code from cell F to that we need to tag on to. And this is where we use encode U R l What it does, basically, is to take your data on converted into a format which is Earl legal. In other words, any characters which were illegal in a URL will be converted to their equivalence. So, for example, spaces will be converted to percentage 20 so just not enough to we closed quotes, then use ampersand. Then we use encode U R L click on it to on close parentheses to end the function. Now we need another ampersand on We reopened the quotation marks. So the whole thing looks rather strange because, of course, we can an M percent inside this string, but as you probably know, 1% a very common in you RL's Well, you're supplying parameter values. Then, of course, the other change we need to make is to replace Demo with our user name. Let me close quotes, close parentheses that we're done. We can then copy that formula down and each of the cells in column h will now contain a slightly different XML document. What I always like to do at this point is to copy one of these XML documents into a text file so we can take a good look at the external structure. So let's go back into the folder and create a text document. It's just rename it result dot xml. Yes, we do want to change the file extension and then we can just open it and paste in our XML. So here's our X terminal document. We can lose the double quotes so we can see we have a Roose element of geo names, and then it's got to child elements. So it'll result count and code. So let's just select all of these and tap them in, and then code has child elements that contain the data that we actually want. So again, let's just tap those in on the two bits of information we want their Agassi lacked on LNG. So in terms of our experts statement, we're gonna have three location steps G any names, code a man, L A T or L N dri, and in each case, we maybe along the default child access so they can from parent to child each time that's save this document. And now let's get back to excel so we can hide column H That's Desprez escaped to come out of copy mode and then create a formula. So we've got equals Filter XML. The first parameter is the XML, which is in our hidden cell H two on the second parameter is our expert statement which goes inside double quotes. For an absolute experts statement, you always start with a forward slash, which represents the root of the XML file and then you put your location steps. So first, as we said, was geo names. Then we had a code, and then the latitude is L. A t close quotes close parentheses and we can just copy this inside formula because the longitude is virtually the same. But let's just paste it in for longitude. Maintain l a t i N g. And we're good to go. So we can now coffee this formula down for every road on because basically, you know, hidden column. We return in a slightly different XML result on each road, the experts statement is retrieving a different value each time. So that's our first look at using expert statements with the Filter XML function on. Just remind you, we have these three locations steps in each case we use in two off the three possible components we use in the access on the no test. We're not using the optional predicated. It will come to that later. And as I said, because the child access is the default, it's normally permitted. So if you did want to write it in, it would be child Cohen Colon. In each of these three cases, you will see you get exactly the same result. But of course, because child is the default axis, it's always omitted, so it's just undo. Although the Filter XML function is normally used in conjunction with the Web service function, it's also possible to use it on local XML files. I will take a look at doing just that in the next video. 24. 04-04: Using FILTERXML with Local XML Files: Although the filter exceptional function is designed to work with Web services, you may occasionally find it convenient to use it with your local XML files in this video. We'll look at a work around for doing that. It's going to sub folder before using Filter XML with local XML files. Let's open up. The Excel file will be working with expense claims. So here we've got a bunch of expense claims by staff members. We want to do the equivalent of a V look up to find out the I. D first name and last name off their head of department. But we want to do this from an XML file. Obviously, in some scenarios you'll be able to simply import the XML file into a table on then USVI look up index match as appropriate to pull across the data that you need. But as we've seen, the all XML files lend themselves to being imported into an Excel table. So there will be times where it's more convenient to connect to the XML file externally and then to use the Filter XML to pull in data from different parts of that XML structure. There are a couple of limitations. So let's have a look of the work around that we're gonna use. Let's create a new work Feet called XML Connections. And the first thing I need to tell you is that this work around has one key limitation. And that is because we use in Filter XML, which takes as its first parameter an XML document. The XML file that we connect to can't have more than 32,767 characters because that's the maximum number of characters which will fit within a cell in Microsoft Excel and for the Filter XML function to be used, we have to have the entire XML document in one cell. So we go across the data, the feature that we use it, get external data and we simply connects to the XML file as a text file on this operational course, excel to import the XML, and under normal circumstances, it will take up several rows. What we want to do is to have it all imported into a single cell. So the first work around is to use what's called a min ified XML file. Let's go back into the train in folder so you can see what this means. Here's the XML file that will be connecting to hence the XML. But as I've said, if we import this, it's gonna be imported on lots of different rose rather than into a single cell. So what we've done is to convert it into what's called a min ified XML file one with all the spaces taken out. So we open up heads magnified. It's exactly the same document. But as you can see, all the returns, tabs and spaces between the elements have been removed. So it's just one long string. This means that when you connect to a text file in this format, it all comes in into a single cell. So it's close. Both of these let's get back to excel and connect to the mini find version. So in the data tab of the ribbon, we go suit, get external data from text. Let's just work out waiting to fold a number four view along files the man connect to heads . Many find because we treating it as a text file. The text import was it appears so in step one, we specify the Limited instead to will say that the delimit er is other, without specifying what it is. And just in case. Let's say that the text qualify is none, and then instead, three we can choose. Text is the format, then we click on finish before clicking. OK, it's a good idea to have a look at the properties because there's one property which is rather irritating on that is that every time you refresh the data, you're prompted to specify the location of the file. No idea why that's the default, but let's switch it off. And then, as you can see, you can specify in the frequency of the refresh and you can specify to repress the data every time you open the file, the data formats and of course, worms apply. Because we're not bringing this in in a tabular format, we're bringing it into a single cell, so click OK when we click OK, again. The entire document is now being brought into a single cell. If I go out to the home tab and speech onward ramp, we can see our many fine XML format. There are several websites available which will create a unified version off your XML. We're going to the training folder. I have the u R l of one of them XML many fire. So if you want to experiment with this feature, just visit this u R L Paste in the original XML. It's like on many fi and you'll have a unifying version off your file toe work with. So now that we have our XML in a single cell, we can go back to expenses and use Filter XML to navigate our way through it. So let's open the UNM unifying version so we can have a look at the structure so we can see that we need to navigate from heads to hand and then to stop i. D. Last name first name on we using underscore to split words. So let's put the basic formula in equals. Phil drinks ML. The XML document is in our XML connections. Work feet. When we click on the cell that contains the linked document, you'll notice that it's the name of the link document that gets put in because that name has been applied to the cell that we put our expand statement or would slash to indicate an absolute path. Heads slash head slash stuff on the school i d. Close quotation marks can be close in parenthesis. When we enter what we've actually targeted, we'd go back to the XML structure is the staff i d of the first of our heads of department . In other words, out formulas not quite finished because we're not hoping in on a particular department or a particular branch, But we'll leave it like that for this video. And then in the next video, we'll finish it off when we talk about X path predicates. But for the moment, let's just copy the formula and paste it in so we could pick up the first name and last name. Since I've said, these three formulas are purely picking up the details of the first person. And if we were to copy it down, we simply get that person's details replicated for every room. So let's just don't do save out changes. And in the next video, we'll discuss the use of expert predicated, which will enable us to complete these three formulas 25. 04-05: Using Predicates: in this video, we'll carry on where we left off in the last video. So we go into number four Filter XML, hopefully save your changes. So let's just be the file amoled has pasted into using predicates and then Evan it from there enable content. And this is where we left off. So we've got a formula which basically only targets. The first person in the XML document on what we need to do is to ensure that the person that we actually target is the head of the Finance Department in this case and worse in the London branch. Let's just reopen the XML files from that folder for just to remind you what the structure looks like. So basically the things that we need to test about attributes the department attributes and the bronze attributes off the head element. So let's edit our function on what we want to do is to home in on the right head element, so are predicated. It needs to go on our middle location, step after the head. No test predicates are written in square brackets like so and then between the square brackets, you put the equivalent off the first argument Oven Excel if statement. So in this case, we're testing two attributes when, right in the name of an attribute you simply preceded with an outside on that distinguishes it from the name of an element. So let's start with the Department Department equals and then we need to slot in Cell B two . So that means we've got a close quotation marks. The news M percent. We're working locally, so we don't need to worry about things like you are all in code. And of course, so you just click on B two and per send on. One thing I've for gotten is that B two has to go in single quotes. So just before the close quote, I'm going to insert single quotes. And then, after the ampersand, we opened double quotes and close single quotes. So, in other words, the value and be too will end up inside single quotes. And then we do the same for bronze. So we simply say, and bronze equals single close close double quotes, ampersand to slot in the value in C two emphasis end open double quotes, closed single quotes and then we've already got the close in the square bracket so that middle locations that now has all three possible components has the implied child access. It has a no test, which is head, and now it has a pretty kids inside the Reddick. It we just inserting two values from our excel worksheet. Suddenly we click enter. We now have the correct staff i D. But the person who is head of the violence department at the London branch so we can know can't be all of this and paste it into getting the first name and last name. So now when we come for you down, we have the appropriate details that each of the heads of department at the bronze specified on each road. So that's a very quick example of how predicates he used in an expert statement. So if you're looking at a formula that uses filter XML and you see these statements inside square brackets, hopefully you know have a better idea of what's going on 26. 04-06: The New CONCAT Function: as we've seen the Web service function can't be used on local XML files. On the work around, I've suggested, is to use a unified XML file. What if many find your XML isn't really an option. If, for example, the XML file is over written fairly frequently, you'll have to manually updated each time and get a unifying version. So in this video, we'll look at using the new contract function in Excel to enable you to import on ordinary XML file I non men ified and then can Katyn ate all the roads that it occupies, so it can then be treated as being inserted into a single cell. So that's going to number six. Then you can cat function and open up expense claims. Let's move across to Excel connections on, then connect to the UNMIN ified XML file that we've got here called Head Start XML. And, as you can see, because it's not, many find it will take up 363 rows within an Excel worksheet so they can Katyn eight function can't be used because it has a maximum of 255. Paramus is, however, in Excel, 2016. There's a new function called Can Cat, and it offers much more powerful. Can cast a nation options. Let's have a look at how it works, so we go across the data, get external data from text. Now we work our way into folders six View along files and then import, hence dot xml. We important is the limited. The limiter is other and then unspecified XT qualify and none and we form in the colon has text on before clicking. OK, that's just going to properties on Switch off that pesky option problems for file name on refresh. Let's say we want to refresh every time we get in the file, so we've now connected to our XML file. But when we go back to our other worksheet, we won't now be able to use Filter XML because our XML is distributed across 300 rose. So what we need to do is to assemble the XML into a single cell. So let's just merge a whole bunch of cells. I think we need to go all the way down, um, emergence center vertically aligned to the top. And then in that cell, we use Excel 20 sixteens new can cat function equals Can cat. And what's new is that I can now simply click on column A. Have the function will automatically contaminate the values in each row. And the fact that there are 363 rose is not a problem. I don't have 363 arguments. I only have one argument so I could put if I wanted to another 254 arguments within the cat function so you can see how much more powerful it becomes than the classic Can Katyn, a function that we've had for some years. So when I enter my function and special lead wrap, you'll see that I have my XML file sitting in a single cell. I won't bother repeating the creation off the formulas. All I wanted to show you basically is that if the trick of many find your XML isn't really possible, you can still assemble all the lines of your external document into a single cell. Use in that very useful can can't function. But what if you don't have Excel 2016? Unfortunately, you'll have to do it the hard way, but it's still not super difficult Let's have a look at how it would work. I'm gonna make this column narrower. And then here I'm just gonna put in a column of numbers. We know that there are 363 rows, but let's say we want to go all the way down to 500 encase the XML ends up growing, so I go down to about 500 on. What I can now do is to get Excel to build a really complex formula automatically. So I'm using a series of formulas to build another formula. You'll see what I mean in a moment. So we start with equals on. We want to pick up a one. So we put the A in quotes, and then we say ampersand and click on the one which contains one. So that gives us a one. And then we want an ampersand. So we say Ampersand quotes, ampersand close, close. When I press enter, you'll see we now have a 1%. In other words, are formula needs to say a one m percent a two and percent a three etcetera. It's on the second row, we can now say equals the cell above followed by Okay. Ampersand de to Anderson quotes Empress end. Hopefully you can see where I'm going with this. As we copy it down, it's gonna take the cell above and then just add on the reference for that road so I can just double click to copy it them myth to the bottom on This is now gonna be my completed formula. So I can just copy that into note pad time equals, paste it in there, lose the trailing ampersand, and we've got ourselves a formula. So not a huge amount of work to created, likened a little of this. Let's just make another one of these big cells. So let's win this a bit. Select a bunch of cells and then Myrdal of these cells left the line aligned and hopefully my clipboard is still intact. Suddenly placed all of that in and so on. World run and we've got exactly the same results. Very untidy formula. But as you can see, not so much work to create. So if you haven't got Excel 2016 this is the approach you can take. But if you have 2016 and I'm sure that they can cat function is one that you'll be very keen to start using 27. 04-07: Descendant or Self Axis: and all the examples with you. So far, we've only been working with one axis the child access, which is the default and therefore doesn't need to be specified. In this video, we'll talk about another access that you'll encounter, which is the descendant or self access. So let's going toe hold of five where we finished up our expense claims. Examples. Let's just copy that and paste it into folders seven and then open up the completed file. So here's the formula. We ended up creating and all I want to show you in this video. It's how you can avoid having toe always use an absolute pass, which, if you remember, is one that starts with a forward slash, and that's fine use in the descendant or self access. So let's look festival at the longhand if I deletes. Forward slash heads forward slash and time descendant iPhone along hyphens Self Colon Coghlan's That's the longhand way of specifying the descendants or self access. And if I presenter, you'll see I get exactly the same result on what the descendant or self access enables you to do is in this case to find head elements wherever they occur. within the document without having to navigate through the XML structure, starting with the root element and then working your way down so you'll find Descendant ourself used quite a lot. What you won't find is the longhand that I've just written. I rose it like that just to emphasize that this is the access that we working with. But the shorthand way of writing it is to forward slashes. So whenever you see these two forward slashes, just remember that this is what's going on. Instead of having an absolute path which starts at the document, Root goes to the root element and works its way down. You simply use the descendant or self access to find that element wherever it occurs within a document. So because it is such a useful shorthand, you'll encounter this in tax quite a lot. So hopefully that's just given you more of an idea of what's going on when you see an expert statement containing these two forward slashes. So that completes our initial look at Expo in the next section will move on to look a xsl t on will continue using X path in the context of xsl transformations 28. 05-01: XSLT And Microsoft Excel: in this section will move on to look at one of excels very powerful but also very little known features. And that is the ability to transform an XML document as it's imported into excel. We've already seen that there are many XML documents which don't have a structure that lends itself to being imported into Microsoft Excel and when used in such XML documents in your work flows excess. Lt starches become very useful because they enable you to transform any XML document into a structure which Excel can accept on which can be imported into an XML table within an Excel worksheet. In order to apply an excess, lt style she to an XML document so that you can transform it into a different structure. You need an excess lt process, sir, and the good news is there's an excess lt process of built into Microsoft Excel. The bad news is not a lot of people know it even exists as we've seen. Most of the XML features within Microsoft Excel are accessed by the developer tab, but for some peculiar reason, the excess lt Princess in facility can only be accessed when you use file open and then double click on an XML file. If you use any other method of bringing in you, XML excels excess. Lt features will not be accessible. A second requirement for access in the excess lt features within Microsoft Excel is that you have a process in instruction in the XML document linking it to the excess. Lt style treat in question even if you have an excess lt style sheet available. But you haven't got a process. An instruction in the XML your transforming again these hidden excess lt process in features within Microsoft, Excel will not be accessible. Let's have a look at this process in action. So in the train involving that's going to 05 used in excess lt style sheets and then number one Xsl T and Microsoft Excel. Let's now look at a simple XML document in a style sheet that could be used to transform it . So first of all, that's going to tools dot xml. And here we've got a simple XML document with tools as the root element tool as the repeated element and then a series of child elements which contained the actual data. And at the top of this document, underneath the XML declaration, we have a process in instruction link in this document to another document in the same folder tools that xsl, which is the style shape that will be used to transform it. So this process an instruction is the standard, one that's used to link any XML document to a given style sheet. First of all, we have the name of the process and instruction, which is external style sheet. And then we've got a couple of attributes type specifying the type of linkage and a dress specifying the location off the linked file. Let's now have a look at the style sheet tools that xsl, and the first thing you'll notice is that it's a standard XML document has XML elements, and it's a well hold document. The root element of any xsl style sheet is style sheet, and it's here that will find the name Space Declaration state in the within. This document will prefix all elements and attributes which relate to excess. Lt transformation with the three letters xsl. Hence, the root element is written xsl colon style sheet. Within the style sheet elements we can see to top level elements. The term top level elements refers to those elements, which are Children of the style sheet element. So festival we have outputs, and here we can specify what type of out but we want to create, and the three most common methods are XML, HTML and text. When you're working with Excel transformations, you'll always be used in XML, as the method on the second attribute we have here in dent equals yes means that the output document will be human readable. Invent equals no means that it will be system readable but not so easily read by humans. And then the second top level element is the one that actually is used to do the transformation. And that's xsl template. Each template targets, a specific note said within the Input XML document, from its very important that the first template you define is one which targets the entire document. This means that you won't get unpredictable results in the output document because you have a template which handles everything in the input document. So to define that we use match equals or words slash and that forward slash represents the root of the XML document, which, of course, is the parent off everything inside the document. So what this means is that we have the entire document flowing into this template. And then in this case, we're saying that the first thing that's gonna happen in terms of output is the creation of a new route element called products. So to do that we use in xsl element and then specifying the name of that new element is products so we can see the import document as a rude called tools. We're now saying that the output document will instead have a root called products men. We specify what output we want inside that route. Element on, we do this by itemizing each of the note sets that we want to appear in the output document , and in this case, we using xsl apply templates, which means basically, look for elements called tour and see if there are any templates within this xsl style sheet designed for that element, and do whatever those templates tell you to any part of the input. XML, which isn't targeted by xsl, apply templates, simply won't be present in the output document. Obviously, in our case, tour is pretty much all there is the second template matches the tool element and then inside it. We say that every time we find a tour element will create a new element called product in the same way as we replace. Tours with products were now replace in tool with product inside each product element. We venues xsl copy off to copy the child elements of Tool, and this is what the asterisk means. It means all of the child elements and copy off will simply give you exactly what was in the input. So these two are being changed. Tours is becoming products. Tool is becoming product singular, but these elements remaining the same. They simply have a different parent element on a different grandparents element. So that's a quick overview of what's going on in the template as we go through this section . Obviously we'll get plenty of practice on all of these different techniques and components . But let's leave it there for the moment. And that's now going to Microsoft Excel to look at how the transformation takes place. So, as I said, if you look in developer, none of these commands will give you the opportunity to apply a style sheet to an XML document as you import it. What you have to do is to use file open and access the document that way. So that's going to number five on number one and open tools that XML So here we can see that Excel has recognized that this XML has a linked style feet. So just to reiterate in our XML document, if this line were present, this dialog box would never appear. It only appears when you've got a process in instruction linking the XML document to starchy. And if you have a workflow where there are several different options several different style streets that you might want to use, you simply put a process in instruction for each of them. And then you can choose which of them to use to do the transformation. So obviously we only have one, so we'll click OK on this warning message always appears, So we'll just click on yes and ignore it. And now we're in familiar territory. This is the standard dialog that appears when you import XML. So we'll importers accidental table click OK, and then we have two standard message relating to the creation off a schemer which will in turn generate an XML map, so we click OK and our data is imported. If we move across to develop out and bring up the XML source task pain here, we can see that the routine school products and the repeating element is called product. And if we were to export this document, this is the structure that we now have. So tours and tour on the input document have been completely overridden, and we now have products and products. But I go ahead and click on exports going to number 51 and let's just call these products and his products, and inside it we can see the transformed XML document, so that's a quick overview. Off the process, we have an XML document. It's linked to a style sheet. We use file open to bring it into excel. Excel recognizes that there's an attached style sheet, offers you the option off applying the style sheet. When you do, you have an XML map, which is based on the transformed XML document rather than the original input XML 29. 05-02: Creating New Elements: now that we've had a look at the key principles involved in performing excess lt transformations. But the rest of this section will focus on a particular transformation. Plan 05 used in excess lt style sheets. Let's now move into number two, creating new elements. I've got a couple of pdf diagrams in here which illustrate the transformation that will be doing This is the Input XML document that will be using on. We have encountered a couple of times in the course we have brawn chases the root element, and we're interested in the data. But the problem is that the data exists at three different levels. So we have the branch men into data at one level, the department head data at one level and then the stuff member data as 1/3 level. And for this reason, we're not able to import the XML into a single exceptional table. So what we'll do is to create an excess lt style sheet, but will transform that XML into an excel friendly format. So here's the output document that we want. The root element will be people and inside that will have a repeated element called person , which contains the original child elements that were at three different levels. I will dispense with the photo element. Since this is Exelon, we won't be displaying people's photos and to distinguish between the various staff members . We'll have three new attributes. The role will specify the person's job role and that will just be picked up from the original input document said. We'll pick up by the branch manager, head or staff member, and then we'll specify the branch in which the person works and the department and again, as we transform the document, we'll just pick up that information from these two attributes. So Branch has an attribute called Name, which is the name of the branch on similarly department as an attribute, also called Name, which will be the name of the department. So that's the transformation that we're planning in the section on. We'll just take it one step at a time. So as a first step in this video will look at creating new elements and the syntax that's used within an excess lt style treat for doing that. Let's begin by opening branches dot xml. And now let's have a look at the style sheet So we started with the bare bones. We've got the XML declaration and then the obligatory parent element Xsl style Street with the correct name Space Declaration. So if you remember there to top level elements, so let's just put those in xsl outputs. And then we need at least one xsl templates element, so to finish off the out. But we, first of all, specify Method and Frank, so this is always XML. I'll mention the second parameter just to remind you, but it's not particularly important for Excel, and that's indent. Since we won't see the transformed document, we'll only see an XML table. It doesn't really matter. But I just put that in there just to remind you. Now we come to the template to specify which part of the input document is being targeted by kicking template. We use the match attribute and then for the first template. We always target the entire document by using a forward slashes the value which targets the document root and everything in the document is a child of the document root and just remind you why this is so that we don't have any input which is not catered for by our star trees so we won't get any unpredictable results. So going back to branches, the first thing we want to do is to replace branches with a new parent element called people. So inside our main template, we now use xsl element, which is the element that's used for creating elements. So we have the element element, and then we specify name equals people. So so far, our main template generates just one piece of output, and that is the root element, which we've called people to specify what goes inside it. We would then typically use xsl apply templates, and this enables us to point to any part of the input tree that we want to process. So any part of the input tree that's not addressed will simply be ignored that will not be present in the output document. So this is why it's so important that our first template matches the entire document, because what it means is that weaken suppress anything that we don't want simply by not targeted it with an xsl applied templates statement. So this occasion, when we use xsl apply templates, we want to assemble the branch manager head and stuff member elements. And there's a very useful export operator for doing this, which is the union operator written as the pipe character. Let's see what this looks like. So here we use xsl, apply templates, and then we use the Select a tribute to specify what we're targeting. And as we saw in the last section, we can either put an absolute part, starting with forward slash what we can use this very convenient double forward slash, which is a short cut for descendant or self, and this effectively enables us to jump to any section off the input document. So first of all, we want to jump to branch manager Bronze, underscore Manager. Then we use the pint character to combine branch manager with another section of the input document. So we do forward, slash forward slash head another union character and finally descendant It'll self stuff on the school member. So this fairly short statement is very powerful because it enables us to combine three notes sets from different sections of the document into one note set, and to specify what happens to this combined note said, we now create another template which targets exactly the same note set, so this time I'll match will be the same as the contents of that select. So this template is saying, is that every time we encounter a branch manager, head or staff member element, this is what we want to happen. And of course, what we want to happen is the creation of a person element. And as we've just seen, the same tax for creating an element is xsl element. Name equals person and for this video will leave it at that. So just to recap what we've done, we've created a template that targets the entire input document. And we specified which nodes we want to appear in the output document I use in an xsl apply template statement. And now we specify and what we want to happen to each element within that note set. And so far, what we said is create an element called person. So if the original XML contains 1000 branch manager, head and staff member combined elements, we'll end up with 1000 person elements. At this point in time, it's gonna be an empty element. There'll be nothing inside it, but the element will exist. So that's it for this video. Let's just save out changes. Let's finish by testing what we've got so far. And just to remind you normally you would be going into developer using one of the commands in the XML group. But were you working with XML Transformation? What you have to do is to go to file open and access the XML file that way. So we go across to creating new elements and branches on because it contains the style sheet processing instruction. Excel displays this dialogue that allows us to choose branches but xsl to do the transformation. Then we have the standard warning message. We can leave the defaults XML table option and click. OK, I can understand it message. And here we've got a person column with lots of empty rows and they could go across the developer source. That's what we've got so far. We have people as the root element and then person as the repeating child element. Let's just look at what this looks like as an XML document. So before we can export this, we need to put something in each of these rows, so I'll just put person one and then just company that down. And now we've got something that we can export export older, too. Let's go with people and let's have a look at the result in documents. So that's the XML output that's being generated by our style Street. So far, we've got the people root element and then lots of empty person elements inside it. So in the next video, we'll move on to look at using XSL Copy Off, which is one of the key elements that's used for transferring information from the input document into the Output XML document. 30. 05-03: Using XSL: in this video, look at another key element that you use in xsl style treats. Xsl Copy off That's going to fold the number three Let's carry on Working on branches Don't xsl. So this is a Sfar, as we got within the last video. We have a root element, cool people, and then we have a repeated element called person currently with nothing inside it. The xsl copia of element is used whenever you want to transfer content from the Input XML document to the output exactly as it is without any modification. If we go into the XML document when we talk, get stuff member head or branch manager elements, we want to copy the child elements without making any modification. However, there are a couple of exceptions for a start we don't want. The photo element on the second issue is that we can't copy the staff. I d as is because we scroll down, we'll see that there are examples of star fight is that begin with zero, so you want to import them into excel. Zero would simply disappear if there's a scheme, a document that's attached to the XML, which specifies that this element is Text Excel, but always respect that will therefore retain the leading zero when you're doing the transformation because you're not normally used in a schemer document, it's up to your xsl style sheet to find a solution to this problem. So for that reason, we don't want to use XSL copy on to coffee. The staff I d. As is because we'll need to modify it slightly so that Excel will be forced to retain the lead in zero. So these are the elements which we do want to copy, as is last name, first name, middle initial start date, gender and profile so he could use the XSL copy off statement six times. But it would be far more efficient and far more useful practice if we say we want all the elements with the exception off staff I D and photo. Let's go back to our style sheets. So the name of the element is xsl copiapo, and then you use the select a tribute to specify what you want to copy. So will lead an expanse location step, and our starting point will be the context that's been set by the template because everything we say is taking place in the context of this particular template, as opposed to this one. So the context says we will either be inside a branch manager, head or staff member element. So whatever experts statement we put starts from that context. Um, all we're interested in is the child elements. And there's a special operator for saying this, which is asterisk. So asterisk means all child elements. And of course, it's a shortcut for Child Cove on cola. If you remember from the previous section because child is the default access, there's no need to write it. So we left it like this would get a little child elements, including photo and staff i d. What we now need to say is, Can we exclude those two elements and leave all the others intact? So this is a job for a predicate, which, if you remember, goes inside square brackets on what weaken goes to test the name of the element. And there's a function which will do just that called name so name and then inside the parentheses. We specify what we're testing the name off, and this time the context isn't taken from the template because we're inside a predicated. The context is taken from what precedes the predicated. So what precedes the critic? It in this case will be one of the child elements, whichever one is currently being processed, so we simply want the name off that element. So in other words, we want an access which will keep us exactly where we are. We don't want to go down to the child level up to the parent level. We just want to stay put. And there's a special access for that called self. So this is the longhand self Polanco along and again. This is one that you'll never see, because the convenient short cut is simply adopt. In this case, the DOT simply means we want the name of the element that's currently being processed. Said We now want to say not people, too. Single quotes, stuff underscore i d. Close, single, close and name not equal to single quotes. Photo close, single close. So this is a much more elegant way of saying we want all of the child elements with the exception of the child element will staff i d on the child element called photo and as I've said, the alternative would be simply to have six XSL copy off statements. XSL copy off Select equals first name selected last name, etcetera. So now our person elements will have six elements inside them, and these elements will be completely unmodified. They'll just be exactly as they are with the original element names. So let's say about Changes Mitt across to Excel and just verify that this all works, said file Open. We're going to fold it three and Bronte's, which contains a style sheet link. So he responses that xsl. Yeah, the usual warning messages. So here the sixth Child Elements with the original names. As he concede. There's no staff I D and the photo. And if you go across the developer and have a look at the XML man, we have people as the root I m in person as the repeating element. So so far, so good. In the next video, we'll move on to look at how you could modify the content of an element so that in the output document the contents of the element different to the contents that you had in the original input document 31. 05-04: Using XSL-text and XSL-value-of: in the last video, we looked at the centre acts for copying elements from the input document to the output document without any modification in this video will live on to look at how you can transfer elements from input to output. Modify the contents that you have inside the element. So let's move into folder four years in xsl text and xsl value off. Let's live in the branches don't xsl and this is the style sheet that we finished up with at the end of the last video. So just to recap what we've got so far in our first template, we especially find that we want people as the root element and as the output we want. Branch manager Ed and staff combined into one note set and then, in our second template, was specifying what we want to do to each element within that note set. And what we do want to do is to create a person element for each of them and then place all of the child elements of branch manager, head or staff member with the exceptional staff I D and photo photo. We want to suppress all together, so that's taken care off, but with staff. I d we actually do want the staff. I d We simply want the contents to change. So this time we can't news copy off. Because if we do that, we have both the exterior and interior, um, modifiable. So we always get a replica of the original element. So what we have to do is to create the exterior of the element and then use a different technique to put stuff inside it. So we know how to create an element use element. Xsl element name equals Stop I d And let's just go back to branches. Let me just remind you what we're trying to achieve. We basically want to have an apostrophe in front of the stop i d. So the next cell will store it as text as opposed to a new marrying value. So to do that, we can use the text element. This basically enables you to insert any literal string, thanks a cell text. And then between the opening and closing tax, you can put any literal string that you want. In our case, we want a solitary apostrophe, then going back to branches that xml what we now want is the original contents of staff. I'd be intact and as we said, we can't use xsl copy off because this will coffee, not just the value. It will also copy staff. I d so in excess. Lt, when you want the text inside an element, you use xsl value off. So after xsl text xsl value off and then select equals whatever output you need at this particular stage. So we want the contents of stop I d. So here we need an expert statement and we put in an expert statement in the context set by the template that were inside. So the context is either gonna be a branch manager head all staff member element. And from that starting point, we want a child element. So we're going along the child access, which is, you know, can be written like this, but it's normally simply admitted because it's the default axis. So they go along, the child access on. We look for an element called staff I d. So that's the technique. We reconstruct the elements step by step, starting with the shell xsl element Name equals start by Deeble. Create the shell of the element and put nothing inside it. Then we use xsl text to insert a literal string so we can have the apostrophe and then xsl valuables. Well, look inside stuff I D. On output, that text that it contains. So let's save a man. Just go across to exult. Attest that it's working. So file open. There we go to number four and branches use the style Sheen's and here's our stuff I D. And as you can see, we've got an apostrophe. So Excel will now stole of these numbers as text. So that takes care of all the elements that we want in the output. And in the next video, we'll move on to look at creating attributes. 32. 05-05: Creating New Attributes: in this video, we'll look at how you create attributes in excess. Lt Starfleet's so that's going to fold it five, Creating new attributes and an open up branches that xsl. So we've completed the creation of all the child elements of our person element. So here we've got xsl element in Amy Was Person and then inside it, we've got to stop I d element and then all of the elements, with the exception of staff I. D and photo, that's the elements taken care off, and now we want to create three attributes Roll Branch and Department. So let's start with the role attributes. The role will simply be taken straight from the input document, and it will either be branch manager, head or staff member in a similar way. Branch will be taken from the name attribute of the branch element and in a similar way, department will be taken from the name attributes off department. So that's not great. Our first attributes the attributes, of course, go inside the element to which they're related, and that element is person. Let's just put a couple of spaces into highlight. What's inside? What, so here we've got the element person and inside it we haven't element cold stuff I d. And then we use an xsl copy all to generate all of the other elements. So let's now place out child attributes in here. And as you can probably guess, justus, you have xsl element. The created elements you have xsl attribute, creating attributes, name equals role, and then you can use xsl valuable to specify what you want to go inside. That attribute on the role will simply be the name of the parent element branch manager, head or staff member. So although it will be the parent of role when we put our experts statement, our starting point is always the context that set at the template level. So in other words, we want the name of the element. At that context, we don't need to live down the child access to find it. We're already at the right position, so we use name and then in parentheses. We want to say self Cohen Coulombe, which has the convenient short cut dot and we think it's a good idea to get rid of the underscores and perhaps replace them with spaces. We can use the equivalent of excels. Substitute function, which is translate, works in a similar way. So translate the original string, which is provided by the name function comma and then in single quotes. What do you want to get rid off? What you want to replace younger school comma and in single quotes space and then the clothes in parenthesis. That's our first attribute. Let's move on to the branch and let's just open up branches that XML remind ourselves where the name of the bronze lives. So the name of the bronze is an attributes of the branch element, but the relationship of that element to the elements that we've combined into one notes that is different. So the branch Manager name is an attribute of the parents. Where is when we go down to head and start member? We've got a mover, something like four or five levels. We've got to go to staff department departments and then to branch, so the parent access is rather inconvenient on this occasion. Fortunately, however, experts also has an ancestor axis, so with the ancestor access, it doesn't matter how many levels up you have to go. You're still talking about an ancestor so branch is an ancestor of branch manager, and it's also an ancestor of head and Start member. The number of levels you have to go up to get from one element to the next becomes irrelevant. So let's go back to our style sheet. So value off can now use ancestor, and this time there is no convenient shortcut, so you have to type it longhand, As you probably remember. When you type the Axis longhand, it finishes with two Coghlan's. So we go along the ancestor access and look the branch. That's the first location step. So we finished with a forward slash and move on to the next location step and the next access. And, of course, having got to brunch, the next access is the attributes. Access have a long hand for that is a tribute coat on colon. But this time there is a convenient shortcut, which is the at sign. So we simply say at name. So, just to recap, we've got to location steps, the first using the ancestor access to move up an indeterminate number of levels. Then the second lives along the attribute access to find the attribute Cool name. So that's two of our three attributes completed, The 3rd 1 is a bit more tricky. We want to find the department of all of these people. In the case of head and Staff member, that's no problem. They both have an ancestor called Department, which has an attribute called name detail in the name of each department. But Branch manager is on the level above departments. So in other words, the branch manager is not the descendant at any particular department will probably want to put something like, not applicable or if in our organizational branch managers were part of the administration department, we would put administration for all of them. But the point is, we want to process Bronze manages differently to the way in which we process head and staff member elements. So for that, we'll need a conditional. We'll talk about conditional in the next video. So for the moment will stay with these two attributes, roll on branch and just verify that they're working correctly. So let's say that changes I live across to excel a man just use file open to check whether it's all working. So number five. Let's open up branches applying to style sheets on generates our XML table, and here we can see we've got our role branch manager for the first head for the second and then start member until the department changes when we have another head and so on. So everything's is it should be. And if we go across the developer and look at the XML map, you can see there's a subtle difference between the icon next to roll in Branch and the icon next to all of the others. So we have an attribute icon next to Roll and Brandt, and we have an element icon, which is slightly lighter in color next to all of the others. And as we saw before, we have our people and person elements replacing the original branches, etcetera. So so far, so good. And in the next video, we'll move on to look at the use of conditional statements 33. 05-06: Using XSL-if: Let's now move on to look at the two conditional statements that you can use with xsl style sheets, the first of which is xsl. If let's move into under six and then open up branches, don't xsl where we can see our file as we left it in the last video. So he got out two attributes, and the third attribute was the more complicated one. Because we go back to our XML, we want to process the branch manager element differently to the way in which head and staff member elements of processed there are two conditional elements in xsl xsl. If an xsl choose excess, I live is the simpler of the two. It is similar to excels if function, but it's simpler with excels. It function. You have a test, a value of true value, false with xsl. If you have a test and then you do something. If the test is true, if the test is false, nothing happens. So in other words, you can only do something. If the test is positive when the test is negative, nothing occurs. So let's have a look at how it works. So we created our third attributes which has a name of departments on what we want to do, is to test the name of the context element. Is it staff member, head or branch manager? And if the name is not branch manager, then we want to move up the ancestor access to find the name attribute of the department element. So we use xsl if and then instead of select used test. And then we can use the name function that we've seen a few times Name of the current context element not equal to which we've also seen. Men in single quotes Franch under school manager Close single. Close. So you close out if and then between the open and close in tanks. We specifying the output that we want xsl value off and then select, will equal ancestor access for which there is no shortcut. This time we're looking for department end of location steps forward slash switch to the attribute access by using the at sign and again the attributes is called name. And that's all we can do with the If statement, he is out test. And here's what we want to do if the test is true. But xsl if doesn't provide a mechanism for saying what we want to do if the test is false. So we happy to say that if the name is equal to branch manager, do nothing. In other words, leave the value empty, then we're home and dry. But if we want to put a value, for example, you want to say not applicable or administration. Ben will need to use the other conditional provided by xsl, and that's xsl choose. So that's it for this video. Let's save and just test that it's still work in. So they were in number six, Uh, so if we look at the branch managers by filtering on the roll, we can see that none of them have a value inside the department. Where is everyone else? Does have a value. So that's how xsl if works in the next video, we'll move on to look at it slightly more sophisticated partner in crime xsl choose 34. 05-07: Using-XSL-choose: in this video, we'll take a look at the xsl choose element, which allows you to happen if else if functionality. So it's going to number seven using excess old shoes and again open up branches. Don't xsl where you can see the file as we left it in the last video. So so far we have a single test, but we have no mechanism for test into the alternative occurrence. This is where xsl choose becomes useful so we can still use although code we have so rather than the late in it, I'm just gonna tablet in and then above it, we'll put xsl choose an xsl chooses just a shell, so we'll need the clothes in tag off the excess and live. And the mechanism that xsl choose uses to do its test in is to have a child element called xsl when so we now need to change this if to a when and it caused the clothes in. Tak needs to be changed as well, so this becomes the equivalent of our if statement. But what we can now do that we couldn't do with the xsl if is to repeat xsl when as many times as necessary to test for different possibilities. On this occasion we only need one test. But what we can also do inside xsl choose is to put a cattle which will only kick in if all the preceding xsl went tests proved to be false. So obviously, on this occasion there is only one xsl went test. But the point is we could have as many as we like and then to say if all of them are falls due the following we used xsl otherwise And of course, this is not a test We've done all the test in so xsl otherwise simply means if all of the tests proved to be false so obviously out test Waas name is not equal to branch manager. Hence the otherwise will pick up the circumstance when name is equal to branch manager. And in that circumstance, let's say that we want to display the text not applicable. So because this is a literal string, we don't need xsl value off. We can just use xsl text and then we have opening and closing tags on. We don't need any double quotes weaken Just put the literal stream. So that's how xsl choose works. The xsl choose itself is just a shell on then xsl, when with test equals is used to test for all the possible values that you're interested in , check in for and then you use xsl. Otherwise is a cattle which will only kick in if all of your xsl went tests are false. So again, let's save on test. So this time, when we display, only the branch manager rose older than have not applicable as the department entry. So that completes our look at condition ALS and to finish off this section and indeed to finish the course will stay with attributes that never look at a very useful feature called attribute found you template. 35. 05-08: Using Attribute Value Templates: in this section, we looked at the basics of working with excess lt style sheets, and we've done so in the context of imported XML data into Microsoft Excel excess. Lt is a very deep topic, and excess lt style streets can be extremely complex. And as you can imagine, we're only just scratching the surface. But no look at excess. Lt is complete without the final topic that we're gonna cover, which is attribute value templates. So that's going to sub folder number eight and just have a look at how they work. So the local branches, the xsl and this is where we left off in the last video. And let's focus on the section of the style sheet where we specifying the output that we want to generate and the two elements that leave used to do that xsl element on xsl contribute as well as using these two elements access. Lt also permits you to use literal elements, so to create a person element instead of sane xsl element name equals person. You can simply type a person tag like said, and then obviously you need to close that person tang on the bottom and that will produce exactly the same result. So let's do that for the other elements that were output in stuff I D. And then we'll stroll to the top and do the same with the root element. The main benefit of using literal tags in this way is that your code tends to be shorter and it begins to resemble the output that you're created. So you've got a better idea of what the output will look like as you're creating your style sheet on. This becomes even more the case when you do the same thing for attributes. So instead of using xsl attributes, you can actually write the attribute inside the opening tag of the element to which you want to attack it. So here we would just put the name of the attributes, which is role equals double quotes. Then we can do the same for Branch. And then inside the double quotes, we have the option of inserting the expats that we have inside xsl value ALS. So I can now copy this expert statement. But if I was simply to paste it inside these double quotes, I would be implying that I want literally what it says inside the quotes. So I need to find a way to tell the excess lt process er that what's inside these double puts is actually an expanse statement that needs to be processed accordingly. On the mechanism for doing this is called an attribute value template on the syntax for indicated an attribute value template. It's curly braces, so you find now paste my expert inside those curly braces. I've indicated that this is X path as opposed to a literal string, and I can do the same with the value off the branch, Elhanan said. It's copy all of that and then paste it inside curly braces. So now these to attribute declarations are surplus to requirements, I could get rid of them. So now, as I'm looking at the xsl code, I can get a much better idea of what the output documents gonna look like. So here I'll have, like people root element, then inside that have a person element inside of which have role and branch. Now, as you can imagine, I can't do the same thing with the department attributes because I've got a complex definition of what needs to go inside, so we'll need to leave my xsl True's intact inside the xsl attributes element. But wherever I have a simple xsl value off statement that I can use this mechanism of doing a literal definition off my attributes and then using this very useful attribute value template mechanism to specify the experts that will generate the content for that attribute . So that's what attribute value templates are, and that's how they work. Let's just save and do one final test in Excel to make sure that everything still works. Uh, and as you can see, everything is still working on. We go across to developer. Look at our map were still there all three attributes. So just bear that in mind. You've got two ways of working. You can either use xsl element and xsl attribute, or you can use literal strings to define your elements and your attributes and then used attribute value templates to specify the value off each of those attributes. So that completes our look at excess. Lt. And indeed, it completes the course. Hope you found it useful. I'd appreciate any feedback that you have. And if you have any questions, feel free to ask. So thank you very much for your time. And I hope to meet you again on another calls. Goodbye