How to Create a Schema in Excel (with Detailed Steps)

The Schema-based XML files can help us map the entire worksheet and create a new XML file in a short period of time. If you are curious to know how you can create a Schema-based XML file in Excel, then this article may come in handy for you. In this article, we discuss how you can create a Schema-based XML file in Excel with an elaborate explanation.

Overview of XML Schema Format

Definition

Creating a Schema requires analyzing the structure of a document and attributing definitions to each and every structural element.

Building Blocks of an XML Schema Document

Every single Schema document has some fixed components.

• Elements and Attributes.
• Serial of the child element and the number of them.
• Data types of those multiple elements and attributes.
• The fixed value of those elements and attributes.

Data Types Used in XML Schema

• Time
• Date
• Integer
• Decimal
• String
• Duration
• Boolean

Benefits of Using XML Schema

• Explaining acceptable document content is relatively easy with XML Schema type
• Validation of data is simpler
• Data facts definition presentation.
• Data patterns are easier to demonstrate.
• Data conversion is easier.

Disadvantages of Using XML Format

• XML syntax is complex and verbose in a lot of cases, compared to other text-based formats.
• Array won’t be supported in XML.
• The document size of the XML files is larger compared to other file formats, because of the text-heavy nature.
• At the same time, the XML document is less readable.

How to Create a Schema in Excel: Step-by-Step Procedure

We are going to use the below dataset to demonstrate how you can create a Schema-based XML file. In this process, you need to add the Developer tab in the ribbon if haven’t it in the ribbon. We also need to use the Notepad application outside of Excel.

Step 1: Enable Developer Tab

Before we delve into creating a Schema-based XML file in Excel and later mapping with it, we need to prepare the dataset and the worksheet ribbon for that

• To begin, we need to check whether the Developer tab is present in the ribbon or not.
• If the Developer tab is not presented in the Ribbon, then we need to add the Developer tab from the Options manually.
• To do this, click on the File on the corner of the sheet.

• Then on the Startup page, click on the Option.

• In the next dialog box, click on the Customize Ribbon.
• Then from the right panel, notice that the Developer check box is not ticked.

• Tick on the Developer check box and then click OK.

• After that, you will notice that the Developer tab is now in the ribbon.

Step 2: Prepare Schema File in Notepad

After we have the Developer tab in the ribbon, we can create the Schema-based XML  file in Notepad.

• Observing the dataset, we need to prepare a Schema-based XML source file in Notepad.
• The Schema file will dictate the structure of the XML file and we need to do this outside Excel in a Notepad editor.
• The code presented below:

• The code has several components. Each tag carries a separate meaning.
• The first line is mandatory for all types of Schema-based XML files.
• We put the column header of our designated file in the opening and closing tag inside the record tag.

Step 3: Load and Map Schema File in Worksheet

At this stage, we have the Schema XML file ready. So we can load that file and map the worksheet.

• Then go to the Developer tab and click on the Source. 
• Then from the side panel, click on the XML Maps.

• Then a new dialog box named XML Maps will appear.
• In that dialog box, click on the Add button.

• A File Explorer window will open, from that window, select and load the XML Schema file that you just created just before.

• In the XML Maps dialog box, now the Schema file is loaded.
• Click on the file to select the file and then click on OK.

Step 4: Replace Column Header

As we have the Schema file loaded, we can now drag and replace the existing columns in the sheet.

• Now we have the column headers from the Schema-based XML file in the side panel.

• Then drag the first item in the side panel to cell B4.

• Repeat the same process for the other column headers.
• So we successfully mapped the column headers from the Schema-based XML file to the Excel worksheet.

Step 5: Export Excel File and Observe Output

The mapping of the worksheet is done. Exporting and then opening the file will have the desired output.

• Then from the Developer tab, click on the Export command.

• Then there will be a file explorer window where you need to choose the destination of the output file.
• Choose the folder and then click OK.

• Then head to the Output file save location, and then open the file.
• The file will look like the below image.
• Thus we mapped the Excel file with the help of a Schema file and then export the result to get the final XML file.
• The final XML file will look like the below image.

Download Practice Workbook

Download this practice workbook below.

Conclusion

To sum it up, the issue of how we can create a Schema-based XML file in Excel with step-by-step instructions.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

Related Articles

• How to Remove XML Mapping in Excel
• How to Create XML Mapping in Excel

<< Go Back to XML Mapping in Excel | Export Excel to XML | Export Data from Excel | Learn Excel