• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / XML stuff / Create Xml from Schema

Create Xml from Schema

2013/10/04 by Frans

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.

Main Flow
Main Flow

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:

Namespace Error
Namespace Error

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 node was created where two were expected since the minOccurence restriction of addressline is set to ‘2’.

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.

Filed Under: XML stuff Tagged With: MSXML, schema, SOM, xml

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in