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. In this article, I will discuss how to create an XML schema appropriately using data in Excel. So, let’s begin.

What is an XML Schema?

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 for 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 rows of 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

Steps to Create an XML Schema for Excel

Now let’s create an XML schema for the following dataset.

To create an XML schema for Excel, follow these steps:

1. Type the basic part of an XML document. This step includes typing the mandatory header line, opening, and closing body tags, and mandatory ‘record’ tags.
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.

• An Overview of XML in Excel
• How to Map an XML File in Excel (Complete Guide)

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 you 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 have talked about the XML Schema in Excel. The purpose of this article is to show you how to make an XML Schema from scratch. However, if you have some queries regarding the XML Schema for Excel, feel free to comment below. Have a nice day.

Frequently Asked Questions

Can Excel generate XML?

Yes, Excel can generate XML. You can create an XML file from an Excel spreadsheet by using the “Save As” option and selecting the XML file format. This allows Excel to export your data into an XML format, making it compatible with XML-based applications and data interchange.

What is XML schema generator?

An XML schema generator is a tool or software that automatically creates an XML Schema Definition (XSD) based on a given XML document or dataset. This schema defines the structure, data types, and constraints for the XML data. XML schema generators are helpful for ensuring data consistency, validation, and documentation, as they provide a schema that describes the expected format of the XML data, making it easier to exchange data and maintain data integrity.

What are XML schemas in Excel called?

In Excel, XML schemas are typically referred to as “XML Mapping” or “XML Data Mapping.” XML Mapping allows you to define how XML data elements correspond to specific cells or ranges in an Excel worksheet. This mapping process enables Excel to import and export data between XML and the spreadsheet, facilitating data exchange and integration.