How to Create an XML Schema Using Data in Excel
An XML schema defines the structure of an XML document. To export an Excel file as an XML file, you need to create an XML schema. Without creating an XML schema it’s impossible to export data from Excel as an XML document. Thus, it’s quite important to create an XML schema properly. This article will teach you how to create an XML schema appropriately using data in Excel. So, let’s begin.
What is an XML Schema for Excel?
An XML schema is a document format that defines the structure and validation of an XML file.
It is made up of tags (opening tag and closing tag) just like HTML. Similar to an HTML document, XML also has a body part. Inside the body, each row’s contents of an Excel file are stored.
Each row in an Excel file is called a Record. An XML schema for Excel is made up of those records. The records are the building block of the XML schema for Excel.
Why Need an XML Schema?
To export an Excel file as an XML file, you need to define how the data in your Excel file will be transformed inside the XML file. A schema is a document format that defines how data from an Excel worksheet will be stored in an XML file.
An XML schema works as a map to store data inside an XML document. Thus without using an XML schema, Excel can’t figure out how to export data as an XML file.
- How to Import XML Data from Web to Excel
- 2 VBA Codes to Open an XML File in Excel
- How to Remove XML Mapping in Excel (Quickest Way)
Structure of an XML Schema for Excel
Each XML schema has to start with a mandatory header line. Which describes the XML version and the encoding method.
<?xml version='1.0' encoding='UTF-8'?>
After that, a tag is used to define the body of the XML document. For example, I use the following tag to define the body of the XML file.
<dataset> </dataset>
Inside the body tag, you will use the ‘record’ tag to define each row of your Excel file.
<record> </record>
Inside the ‘record’ tag you will use the column header names as an individual tag to store each cell’s contents.
For example,
<FirstName>Ulrick</FirstName> <LastName>Copner</LastName> <Email>ucopner0@naver.com</Email>
The following picture illustrates the structure of an XML document in a better way.
You can copy the following piece of code to start creating your own schema.
<?xml version='1.0' encoding='UTF-8'?>
<dataset>
<record>
</record>
<record>
</record>
</dataset>Rules of Creating an XML Schema
You need to maintain the following rules while creating an XML Schema for Excel.
- Each column header name has to start with a letter or an underscore. You can’t start a column header name using a number in an XML document.
- You can’t use space in any tag of an XML file.
- You must maintain indentation properly. Otherwise, Excel won’t be able to read your XML file.
- Proper formatting must be maintained while creating an XML file.
- Insert at least two row’s information in your XML schema to work properly.
- 5 Steps to Convert an Excel File to XML File
- 4 Ways to Open an XML File in Excel
- A Complete Guide to Convert a CSV File to an XML File in Excel
Guide to Creating an XML Schema for Excel
Now let’s create an XML schema for the following dataset.
Step_1: Type the mandatory part of an XML document.
This step includes typing the mandatory header line, opening, and closing body tags, and mandatory ‘record’ tags.
Step_2: Insert the contents of each row.
In my dataset, each row has 4 columns. The columns are FirstName, LastName, Email, and Gender.
So I’ve created 4 pairs of tags using the column header names inside the ‘record’ tag. The tags are as follows:
<FirstName></FirstName> <LastName></LastName> <Email></Email> <Gender></Gender>
Inside each tag of the column headers, I’ve inserted the corresponding information.
This is how you can recreate your own XML schema for Excel.
Error in XML ‘Cannot Load the Specified XML or Schema Source.’
When you try to add an erroneous XML Schema as a schema source, Excel shows an error. A small dialog box ‘Error in XML’ pops up upon the XML Maps dialog box that shows ‘Cannot load the specified XML or schema source’.
Well, this error occurs due the several issues with an XML schema. The main reasons are,
- The structure of your XML schema is inappropriate.
- Spaces in the XML tags.
- Tags start with a number.
- Inadequate information regarding the dataset.
- Indentation issues etc.
Now let me show you an erroneous XML Schema. Look at the following XML schema. It has 3 major issues. Those are,
- Wrong indentation.
- Extra Space in the XML tags.
- Information of only a single record where a minimum of 2 records are required.
In this situation, if you try to add this XML schema in the XML Maps dialog box, you will get the warning ‘Cannot load the specified XML or schema source.’
Now here’s the error-free version of the above XML schema. Now if your try to add the following XML schema as a schema source, Excel won’t show the error ‘Cannot load the specified XML or schema source’.
Conclusion
In this article, I discussed the XML schema in Excel. I tried to show you the steps to create an XML schema from the scratch. Yet if you have any questions regarding XML schema for Excel, please let me know in the comment section. Have a good day!
