We all know basic excel skills that help us work in excel spreadsheets with extensive data. But what if you need to transfer the excel file somewhere and change it into other formats?
The extensible markup language or XML comes in handy for this. Simply put, it is a markup language that helps store data in an excel spreadsheet and changes its format.
You can easily convert excel data into XML data – but how do you do that?
For that, download our FREE Exercise Workbook here and continue reading the article below. 😀
Table of Contents
How to Create the XML Schema?The XML file format is a way to write text in a document using tags. These tags are customizable and represent the structure of the document.
It also tells how the XLS file can be converted to an XML file and transferred to another application.
XML format uses the DOM or Document Object Model as its basic structure and can help separate text in HTML.
For instance, XML data for a company employee might be:
The tags ‘Name, ID No., Post, Salary, and Status’ consist of the document’s data. You can customize the tags as we did here for an employee’s data.
To convert Excel to XML, you need to create an XLS file containing all the data. I have created an example below that lists the members of a company and their details.
Each field contains certain information about every employee.
The XML Schema will structure each spreadsheet field and its appearance in the XML file.
Our spreadsheet has five columns with different data types setting the base for our XML file.
1. To create the XML file, open any text editor and begin entering the standard XML elements.
Don’t fret just yet; We’ll explain all the alien-like items in this image one by one. 😄
The first line in the image is the standard XML line. It is used each time you create XML documents.
The version 1.0 encoding UTF specifies that it is an XML format.
the XML filey" width="650" height="47" />
The second line indicates the type of text. For example, this data was about employees of a company, hence employee-data>. The remaining line is the default.
Since this statement starts with , it is the root element here. The remaining elements of the argument make up for the child element.
The third line, containing the record tag, indicates the beginning and end of a single field. It consists of the information in each field of the spreadsheet.
For instance, the first field is about Alice A. So it will include all the information about Alice on each tag.
The TAG NAME specifies the items in each column, whereas the data contains the corresponding value.
You don’t have to manually input the exact and each column name; they just need to be in the correct order. Excel can help us do the rest.
For the TAG NAME, say we want the first employee’s name.
So, we will write –
Pro Tip!
Must note the opening and closing tag identified by a forward slash (/).
These are to be used at the beginning and each of each statement. Also, the closing and opening tags must be in the correct order, as the data between the two tags make for the XML elements.
The last closing tag marks the end of the file. In the last step, save the XML data file as “employee-data.xml.”
Now that your schema markup is prepared, let’s add it to Excel.
1. Make sure the developer tab is enabled on your Excel spreadsheet. If it is not visible, right-click on the ribbon and select Customize the ribbon.
2. A dialog box will appear on the screen. On the bottom right side, mark the develop option as shown and click ok.
3. Now, you should be able to view the developer’s tab on the ribbon like this:
That’s it – now what? Now we add the Schema file to Microsoft Excel. 😃
