Creating Your Own XML Schema Description

One of the requirements for the data entry template is that it needs to be able to edit and add content in an XML document. To meet this requirement, you will have to provide Excel with an XML Schema Description (XSD). Although you could try to create your own XSD from scratch, there is enough complexity around developing XSD files to make that option a rather unappealing one. Instead, you can leverage Excel to create an XSD for you.

First, create a simple XML document using a simple text or XML editor. The goal here is to enter the elements that make up the data points you need to capture. In this example, you want to capture a set of five data points: employee ID, first name, last name, invoice number, and invoice amount. In that light, create the simple XML document shown here:

<?xml version="1.0"?> <EmployeeSales> <Employee>

<Empid>999</Empid> <FirstName>Text</FirstName> <LastName>Text</LastName> <InvoiceNumber>999</InvoiceNumber> <InvoiceAmount>999</InvoiceAmount> </Employee>

<Employee></Employee> </EmployeeSales>

Note that fake data is placed into the elements. This will allow Excel to identify the data type for each element when it infers a schema. You will also note the inclusion of an empty Employee element. This will ensure that the Employee element is tagged as a repeating element in Excel's schema. Why do you need this particular element to repeat? The data entry template will need to accept multiple entries at the employee level; a repeating employee element will satisfy this requirement. Be sure to save your file as an XML document.

XML Map Properties

It's important to note that your XML map has certain properties that can be adjusted to suit your needs. To access the properties dialog box, simply right-click anywhere inside the XML list and select XML O XML Map Properties. Again, later in this chapter, you will discover how you adjust these properties dynamically via VBA.

Name: The name property specifies the name of the XML map.

Validate data against schema for import and export: When selected, this property will direct Excel to validate any data entered into the XML map against its corresponding schema, ensuring that the data conforms to the rules specified in the schema before importing or exporting.

Save data source definition in workbook: When selected, this property ensures that your XML map is linked to the source XML document, thus allowing the document to be refreshed. Deselecting this property will make the XML map static.

Adjust column width: When selected, Excel will automatically adjust the width of columns to fit the mapped XML data.

Preserve column filter: When selected, Excel preserves the sorting and filtering applied to the XML list when refreshing the XML map.

Preserve number formatting: When selected, Excel preserves the formatting applied to numbers in the XML list when refreshing the XML map.

Overwrite existing data with new data: When selected, existing data in the XML list will be overwritten when the XML map is refreshed.

Append new data to existing XML lists: When selected, any new data from the source XML document will be added to the bottom of the existing data in the XML list when the XML map is refreshed.

Next, open the XML document from Excel, allowing Excel to infer its own schema. Once the XML document has been mapped, open the XML Source task pane to inspect the XML map. To do so, select the Developer tab in the Ribbon, and then select XML Source. This will activate the XML Source task pane, shown in Figure 12-5, where you will see the elements that make up your XML document.

XML maps in this workbook:

XML maps in this workbook:

EmployeeSales_Map v

- Employee5ales

- ^y Employee

I Empid

' -1 FirstName

1 LastName

1 InvoiceNumber

_| Invnic ('Amount

Notice that this particular XML map is named EmployeeSales_Map. Every XML map must have a name. When Excel infers a schema for you, it automatically assigns a name made up of the root element name and _Map. You can change this name by adjusting the Name property in the XML Map Properties dialog box. It is generally good practice to use the assigned name when referencing an XML map via VBA.

Excel has successfully created a schema for the data structure you specified via the XML document. Your goal now is to get that schema out of Excel and into an XSD file. Unfortunately, there is no way to extract the inferred schema via the user interface. You will have to use a bit of VBA to get to the schema. Start a new module and enter the following code:

Sub GetSchema()

Dim MySchema As String

'Get the schema

MySchema = ActiveWorkbook.XmlMaps("EmployeeSales_Map")

.Schemas(1).XML

'Create and fill an xsd file with your schema

Open "C:\MySchema.xsd" For Output As #1

Print #1, MySchema

Close #1

End Sub

In this code, you first use the XmlMaps collection to identify the XML map and schema you are targeting. As you can see, the target schema is the primary schema for the XML map named "EmployeeSales_Map" in the active workbook. Next, you create empty text file and save it as "C:\MySchema.xsd". Finally, you output your schema to the newly created XSD file. Your reward for creating and running this code is an XSD file that took very little effort to create.

+1 -2

Responses

Post a comment