In this post I describe a tool to create an Xml file from a schema (Xsd) using VBA and the DOM and SOM objects of the Microsoft XML library MSXML version 6.
When you import a well formed Xml file into Excel, Excel will create (infer) a schema. But now suppose we have a schema and want to create an example of a well-formed (not necessarily valid) Xml file. The first thing you can do is check your professional Xml editor or your IDE (eg Visual Studio, not an Express edition). These tools often can do the job for you. Secondly, you could try one of the online Xsd to Xml Generators. Finally, check out the enclosed workbook. It has a vba procedure that takes an xsd file and creates an empty Xml-file. For example:
<?xml version="1.0"?> <xs:schema xmlns="" xmlns:xs="https://www.w3.org/2001/XMLSchema"> <xs:element name="catalog" type="catalogType"/> <xs:complexType name="catalogType"> <xs:sequence> <xs:element name="book" type="bookType" maxOccurs="unbounded"/> </xs:sequence> </xs:complexType> <xs:complexType name="bookType"> <xs:sequence> <xs:element name="title" type="xs:string"/> <xs:element name="author" type="xs:string"/> <xs:element name="datePublished" type="xs:date"/> <xs:element name="publisher" type="xs:string"/> <xs:element name="addressline" type="xs:string" minOccurs="2" maxOccurs="7"/> </xs:sequence> <xs:attribute name="id" type="xs:string"/> </xs:complexType> </xs:schema>
With this schema as input the procedure creates an Xml-file as follows:
<?xml version="1.0"?> <catalog xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"> <!--book is repeating {1,-1}--> <book id=""> <title></title> <author></author> <datePublished></datePublished> <publisher></publisher> <!--addressline is repeating {2,7}--> <addressline></addressline> <addressline></addressline> </book> </catalog>
Explanation:
- In the Xml file comments are added to give information about special elements (lines 3 and 9);
- The element addressline was added twice (10-11), in accordance with the minOccurence property of this element (18);
- All elements are empty, so most numeric and date elements will be invalid;
- In the schema the default namespace is null. For this tool this is a requirement. Furthermore, there is no targetNamespace specified, which is also a requirement;
- The schema has one element (4) as child of root xs:schema. This is a requirement for this tool.
The main process
The figure below shows the main flow of the process, which basically reflects the structure of a schema.

Note that in this figure the return arrows are omitted. Also note that this is a recursive process.
Explanation:
- Procedure CreateXml prepares the processing of the schema and the creation of the Xml document. The processing of the schema starts with a call to ProcessElement, in which the main element
is processed; - ProcessElement takes an element as input and creates a corresponding node in the Xml document. If the element is a ComplexType then it calls ProcessComplextype. If the element is a SimpleType then ProcessElement ends and returns control to it’s calling procedure (which is either CreateXml or ProcessParticle);
- ProcessComplexType first adds any attributes to the new node by calling ProcessAttribute for each attribute. Next it will call ProcessModelGroup;
- A ModelGroup (or ContentModel) of a ComplexType provides a collection of so called Particles. Basically, ProcessModelGroup calls ProcessParticle for each particle in it’s collection;
- A Particle is of type Element or of type ModelGroup (type Any can be ignored). ProcessParticle calls one of the corresponding procedures ProcessElement or ProcessModelgroup;
- After control has returned to CreateXml, the Xml document is validated against the schema and any errors are written to the immediate window. Finally, an Xslt-transformation reformats/indents the Xml document, which is then saved to the outputfile.
In the process the Schema Object Model (SOM) is used to interpret the schema, and the Document Object Model (DOM) is used to create the Xml outputfile.
How to call
In module MCreateXmlFromXsd you find:
Public Sub CreateXml( _ sSchemaFile As String, _ sOutFile As String, _ Optional CreateValidStructure As Boolean = True, _ Optional Comments As Boolean = True)
Explanation:
- The sSchemaFile, sOutFile an Comments parameters speak for themselves;
- If CreateValidStructure is false then CreateXml will generate all choice elements of a Modelgroup of type SOMITEM_CHOICE. Furthermore, CreateXml will generate exactly one item of every repeating element, ignoring the minOccurence restriction. This setting will almost always generate an invalid document structure, but gives you good insight in the overall structure of the document;
- If CreateValidStructure is true then CreateXml will only generate the first choice element of a Modelgroup of type SOMITEM_CHOICE. Furthermore, CreateXml will create at least one item of every repeating element, and as many as specified by the minOccurence restriction. This setting creates a valid document structure (but not necessarily a valid document).
Errorhandling
The tool has no errorhandling. A runtime error could be something like:

which occurs at the line where the schema is compiled and validated:
xSchemaCache.Add vbNullString, sSchemaFile
To resolve this error you must remove the default- and targetnamespace from your schema.
Validation results
The validation results are output to the immediate window of the VBE. A typical error is
-1072897535 /catalog/book[1]/datePublished[1] Error parsing '' as datatype date. Element 'datePublished' with value '' cannot be parsed.
which is obvious: null is not a valid date.
Another typical error is:
-1072898030 /catalog/book[1] Content of element 'book' is incomplete according to DTD/Schema. Expected: addressline.
Here CreateValidStructure was set to false, in which case only one
Reference
For more information on the DOM and SOM objects of MSXML see Microsoft XML Core Services.
For more information on Xml and Schema in general see the XML Tutorials on W3Schools.com
Final notes
In this article I presented a tool to create a sample Xml file from a schema. However, this tool is also an example of how to use the Schema Object Model (SOM) to interpret a schema, and how to use the Document Object Model (DOM) to create an Xml-file from scratch. In this respect I hope this tool contributes to a better understanding of the XML Schema in general and the Schema Object Model in particular.
Another example of schema processing is the Walk the Som application which you can find on the above referenced MSDN page. The two applications have different goals in mind and take a quite different approach to processing a schema.
I hope this pixcel helps. Excel!
Download
CreateXmlFromXsd-v1.zip
The zip file also includes a sample schema.