XML View Controls in Word and Excel

Article
04/22/2004

One of the features I'm particularly fond of in Microsoft Visual Studio Tools for the Microsoft Office System, Version 2.0 is the automatic creation of schema-specific programmatic object models. That's a fancy way of saying that Visual Studio Tools for Office, Version 2.0 can infer a programmatic object model from a given XML schema. (The tools in Microsoft Visual Studio 2005 (formerly codename "Whidbey") can create XML schemas when you use the new Data Sources window or the existing Server Explorer window, but more about that in a later post.) More specifically in Visual Studio Tools for Office, Version 2.0, when you map XML elements from an XML schema to a Microsoft Office Word 2003 document or Microsoft Office Excel 2003 workbook design surface in Visual Studio 2005, if the mapped element's maxOccurs value is 1 (or not specified, and therefore 1 by default), an XMLNode object (for Word) or XMLMappedRange object (for Excel) is created; if the mapped element's maxOccurs value is greater than 1, an XMLNodes collection (for Word) or ListObject object (for Excel) is created for you to program against. Let me show you more about how this works, using Word as an example.

Let's take this very simple XML schema that I created, representing an e-mail message:

<?xml version="1.0"?>
<xs:schema id="emailSchema"
xmlns="https://www.microsoft.com/schemas/MySchemas/emailSchema.xsd"
elementFormDefault="qualified" attributeFormDefault="qualified"
xmlns:xs="https://www.w3.org/2001/XMLSchema"
targetNamespace="https://www.microsoft.com/schemas/MySchemas/emailSchema.xsd">
<xs:complexType name="emailType">
<xs:sequence>
<xs:element name="from" type="xs:string" />
<xs:element name="subject" type="xs:string" minOccurs="0" />
<xs:element name="body" type="xs:string" minOccurs="0" />
<xs:element name="to" minOccurs="1" maxOccurs="unbounded" />
<xs:element name="cc" minOccurs="0" maxOccurs="unbounded" />
<xs:element name="bcc" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
<xs:element name="email" type="emailType" />
</xs:schema>

A conforming document might look like this:

<?xml version="1.0" encoding="utf-8" ?>
<email xmlns="https://www.microsoft.com/schemas/MySchemas/emailSchema.xsd">
<from>paul_cornell@online.microsoft.com</from>
<subject>Reminder about tomorrow's meeting</subject>
<body>Don't forget tomorrow's 2:00 meeting. If you can't attend, please let me know.</body>
<to>fred_smith@online.microsoft.com</to>
<to>bill_jones@online.microsoft.com</to>
<cc>ted_johnson@online.microsoft.com</cc>
<cc>wilma_smith@online.microsoft.com</cc>
<cc>melinda_jones@online.microsoft.com</cc>
<bcc>nancy_frank@online.microsoft.com</bcc>
</email>

When you attach this schema to Word for example as part of a Visual Studio Tools for Office, Version 2.0 solution (on the Tools menu, point to Microsoft Office Word Tools, click Templates and Add-Ins, click the XML Schema tab, and click Add Schema) and then start mapping the schema elements to a blank Word document, you get a bunch of automatically-generated programmable objects.

Here are the programmable objects that Visual Studio Tools for Office, Version 2.0 creates for you in this case:

<memo> = a single EmailNode object (of type Microsoft.Office.Tools.Word.XMLNode)
<from> = a single EmailFromNode object (of type Microsoft.Office.Tools.Word.XMLNode)
<subject> = a single EmailSubjectNode object (of type Microsoft.Office.Tools.Word.XMLNode)
<body> = a single EmailBodyNode object (of type Microsoft.Office.Tools.Word.XMLNode)
<to> = a collection of EmailToNodes (of type Microsoft.Office.Tools.Word.XMLNodes) containing one Microsoft.Office.Tools.Word.XMLNode object per mapped <to> element
<cc> = a collection of EmailCcNodes (of type Microsoft.Office.Tools.Word.XMLNodes) containing one Microsoft.Office.Tools.Word.XMLNode object per mapped <cc> element
<bcc> = a collection of EmailBccNodes (of type Microsoft.Office.Tools.Word.XMLNodes) containing one Microsoft.Office.Tools.Word.XMLNode object per mapped <bcc> element

Notice that the naming convention for each of these is the parent element name, followed by the child element name, followed by "Node" or "Nodes."

So, I could write code to automatically populate the document that looks something like this (in C#):

...
this.EmailFromNode.Text = "paul_cornell@online.microsoft.com";
this.EmailSubjectNode.Text = "Reminder about tomorrow's meeting";
this.EmailBodyNode.Text = "Don't forget tomorrow's 2:00 meeting. " +
"If you can't attend, please let me know.";
this.EmailToNodes[1].Text = "fred_smith@online.microsoft.com";
this.EmailToNodes[2].Text = "bill_jones@online.microsoft.com";
this.EmailCcNodes[1].Text = "ted_johnson@online.microsoft.com";
this.EmailCcNodes[2].Text = "wilma_smith@online.microsoft.com";
this.EmailCcNodes[3].Text = "melinda_jones@online.microsoft.com";
this.EmailBccNodes[1].Text = "nancy_frank@online.microsoft.com";
...

Notice that the collections here are one-based, not zero-based.

By the way, if you did this in Excel (on the Data menu, point to Microsoft Office Excel Data, point to XML, click XML Source, click XML Maps, and click Add) and mapped the elements to cells, here's what you'd get:

<from> = a single EmailFromCell object (of type Microsoft.Office.Tools.Excel.XMLMappedRange)
<subject> = a single EmailSubjectCell object (of type Microsoft.Office.Tools.Excel.XMLMappedRange)
<body> = a single EmailBodyCell object (of type Microsoft.Office.Tools.Excel.XMLMappedRange)
<to> = a single EmailToList object (of type Microsoft.Office.Tools.Excel.ListObject)
<cc> = a single EmailCcList object (of type Microsoft.Office.Tools.Excel.ListObject)
<bcc> = a single EmailBccList object (of type Microsoft.Office.Tools.Excel.ListObject)

Similar to Word, the naming convention for each of these is the parent element name, followed by the child element name, followed by "Cell" or "List."

You'd get some other objects as well, but more about that, as well as automatic creation of programmatic object models for relational data and other types of data, in a later post.

-- Paul Cornell

-----
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

XML View Controls in Word and Excel

Additional resources