XML Programming

There is a host of new objects for programming XML functions. The following table gives an overview. (Since there are many cross-references among the XML objects, there are many ways of representing the hierarchy. Here the starting point is an XPath object.)

XPath	specifies which XML elemements in a range ( Range ) or columns in a list ( ListColumn ) are to be imported
XmlMap	describes the relation between the XML data and the Excel worksheet
XmlDatabinding	describes the data source of the XML data
XmlSchema	describes the structure of the XML data
XmlNamespace	defines the prefix for identifying related XML elements that form a name space

Simple XML importation: To import an XML file to a particular place in a worksheet, execute the method XmlImport in the following manner:

 Dim xm As XmlMap Dim result As XlXmlImportResult result = ActiveWorkbook.XmlImport("name.xml", xm, , ActiveSheet.[B2]) 

With this, the file name.xml is imported into the active worksheet beginning with cell B2. Instead of a file name, the Internet address of the XML file can be specified.

An optional third parameter can be given to specify whether the imported file can overwrite existing data ( True/False ). This parameter has not functioned altogether satisfactorily in my tests. When an importation was repeated, an error occurred regardless of whether or not Overwrite:=True was specified. A solution is first to delete the previous ListObject that appeared during the previous importation ( [B2].ListObject.Delete ).

In general, a repetition of an importation is seldom of any use. It is better simply to update the data. For this, execute the method Refresh for the XmlMap object.

 xm.Refresh      'update imported XML data 

After the importation, result specifies whether any errors have occurred, and if so, what the errors were. In addition, after the importation, the previously uninitialized variable xm contains an XmlMap object. This object contains detailed information on the relation between XML data and the cells in the worksheet. (We will soon present details on XmlMap .)

If the XML data are not to be read from an external data source, but are already present in a character string, then the method XmlImportXml can be used for the importation. The only difference between this method and XmlImport is that the first parameter must hold actual XML data (instead of an address).

Relationship between XML data and the worksheet: To understand better the internal workings of Excel's XML functions, it is worth taking a closer look at how the XML importation process operates:

• Excel generates an XmlMap object and stores within several subordinate objects all the central information about the XML data: the data source (file name/Internet address), structure of the data (schema), as well as its name space and various general importation options.

  One further remark on the XML schema: If the XML data do not refer to a schema definition ( *.xsd file), then Excel analyzes the XML data and itself generates a minimal schema definition. This is stored in xm.Schemas(n).XML .

• Excel generates a list into which all the data are to be imported. For the individual columns of this list ( ListColumn objects) the XPath object is initialized . This determines which XML-element data are to be imported.

• Finally, the actual importation is carried out.

The following list gives an overview of the most important properties that are obtainable via the XmlMap object (here xm for short):

XMLMAP PROPERTY	DESCRIPTION
xm.AppendOnImport	Specifies whether at the next importation data should be appended to (rather than being overwritten by default).
xm.DataBinding.SourceUrl	Gives the file name or Internet address of the XML data.
xm.ImportOption	Specifies whether at the next importation existing cells should be overwritten or moved to the left or down.
xm.IsExportable	Specifies whether the data can be exported after a change.
xm.RootElementName	Gives the name of the root element of the XML data (often "dataroot" ).
xm.RootElementNamespace	Refers to the XmlNamespace element for the root element of the XML data (corresponds to xm.Schemas(1).Namespace ).
xm.Schemas(1)	Refers to the schema (description of structure) of the XML data for the root element.
xm.Schemas(n)	Refers to any schemas (with n>1 ).
xm.Schemas(n).XML	Contains the XML code for the schema.
xm.Schemas(n).NameSpace	Refers to an object for describing the name space of the schema.

The following list gives an overview of the most important properties that are obtainable via an XPath object (here xp for short):

XMLMAP PROPERTY	DESCRIPTION
xp.Map	Refers to the XmlMap object that describes the origin of the data.
xp.Repeating	Specifies whether more than one suitable result can be imported ( True/False ).
xp.Value	Specifies which XML element should be imported from the XML data stream. The syntax of the search expression must conform to the XPath standard; see http://www.w3.org/TR/xpath

Tip  

If you are analyzing an existing worksheet and wish to locate all cells and ranges of cells that are linked to XML data sources, you can use the method XmlDataQuery or XmlMapQuery . These methods return the Range object of the cells whose XPath objects are initialized according to the search criteria.

Controlling XML importation more precisely: With the importation process just described, ActiveWorkbook.XmlImport forces the XML data into a tabular format. This usually succeeds, but depending on the structure of the XML data it can happen that many XML elements will be repeated in every cell of the table. Not only does this look strange , but it also makes a later exportation of the (perhaps altered ) data impossible .

A solution can be to associate certain XML elements with particular cells or ranges of cells. (With manual importation you would use the option Use The XML Source Task Pane.) In code, you would first generate an XmlMap object and set its properties, then set the XPath objects of the cells or columns into which the data are to be imported, and finally execute the actual importation with xmlmapobject. Import .

The following lines of code demonstrate the main points. We assume here that the XML file contains a sales report of a branch of a business for a particular date. The XML elements location and reportdate appear in the XML file only once. On the other hand, there are many sale elements that describe the sales (consisting of product and quantity ). A corresponding XML file might look like this:

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <dataroot>   <location>Graz</location>   <reportdate>2003-01-01</reportdate>   <sale>     <product>P23423</product>     <quantity>12</quantity>   </sale>   <sale>     <product>P924</product>     <quantity>4</quantity>   </sale>   ... </dataroot> 

The goal is that location is to be imported into cell B2, reportdate into C2, and the order list into the range A5:B n (where n is the number of orders). Moreover, in cells A5 and B5, titles are to be displayed; see Figure A-1.

Figure A-1: XML importation via VBA code

 Sub  myXmlImport  ()   Dim xm As XmlMap   Dim xmlfile As String   Dim lst As ListObject   xmlfile = ThisWorkbook.Path + "\test.xml"   Application.DisplayAlerts = False   Set xm = ActiveWorkbook.XmlMaps.Add(xmlfile)   Application.DisplayAlerts = True   With ActiveSheet     .[a1] = "Location:"     .[b1].XPath.SetValue xm, "/dataroot/location"     .[a2] = "Reportdate:"     .[b2].XPath.SetValue xm, "/dataroot/reportdate"     Set lst = .ListObjects.Add(, Range("A4:B5"))     lst.ListColumns(1).XPath.SetValue _       xm, "/dataroot/sale/product", Repeating:=True     lst.ListColumns(1).Name = "Product:"     lst.ListColumns(2).XPath.SetValue _       xm, "/dataroot/sale/quantity", Repeating:=True     lst.ListColumns(2).Name = "Quantity:"   End With   xm.Import xmlfile End Sub 

A few notes on the code: XmlMaps.Add actually expects a *.xsd file with the definition of the schema for the XML data. If a schema definition is unavailable, then alternatively, an XML data file can be specified. Excel then generates a schema definition on its own, but first displays a warning. DisplayAlerts = False suppresses this warning.

Then for a ListColumn or Range object the method .XPath.SetValue can be executed in order to specify the desired search expression (XPath syntax) and the underlying XmlMap object. If name space prefixes are used in the XML data or the search expression, then they must be declared in the optional third parameter of SetValue . An additional optional parameter specifies whether any additional suitable results should be imported as a list ( Repeating:=True/False ).

The actual importation is finally carried out by the Import method, where as parameter the file name of the XML file is given. Normally, you should evaluate the return value of this method to assure yourself that the importation has proceeded correctly. If the XML data do not come from an external file but exist already as a character string, then use ImportXml instead of Import .

The procedure myXmlImport functions only a single time. The attempt to execute the code a second time breaks down because of the existing connection to the XML data. Therefore, you have to delete the XmlMap object as well as the associated cells before a new importation. If you want just to update the data, it is much simpler to execute the method Refresh for the Xmldatabinding object.

 xm.DataBinding.Refresh 

The process that is described here can be used to search and filter data from an XML data stream. The only difference with respect to other XML libraries is that the data that are found must always be imported into a worksheet. (It is impossible to search for a particular element in a data stream and store it directly into a variable.)

XML Exportation: If the property IsExportable of an XmlMap object contains the value True , then the data contained in a worksheet can be rewritten to an XML file. However, IsExportable contains True only if no hierarchy problems are obtained during importation. (Such problems are expressed mostly in that XML elements in the result list must be repeated several times.)

For exporting, execute either xmlmapobject . Export (exportation to an XML file whose name and address are given) or ExportXml . In the second variant the XML code is copied to the character string variable specified as a parameter:

 Dim xmlfile As String Dim xmlstring As String xmlfile = ThisWorkbook.Path + "\testout.xml" xm.Export xmlfile       'export into a local file xm.ExportXml xmlstring  'export into the variable xmlstring MsgBox xmlstring 

XML Form: In Chapter 9 I have presented several possibilities for using Excel for creating intelligent forms. The input data can then be stored depending on the type of data and the programming of the form as an Excel file or in an external database.

Excel 2003 offers an additional variant with its XML functions: The input data can be stored as an XML file. This makes possible further processing with XMLcompatible programs in your work environment (and indeed, particularly if these programs are not from Microsoft and cannot deal with the *.xls format).

To prepare such a form you have merely to create the desired XML schema. This can be done in program code as described previously. It is usually simpler to set up the form in interactive mode. Then you add a button to the worksheet for saving. In the associated code you simply call the Export method for the XmlMap object. (Of course, as a rule, you first carry out a plausibility test for the input data, and perhaps give the user the opportunity to determine the name of the XML file; but the principle should be clear.)

XML Events: Before and after an XML importation or exportation, for Workbook objects and Application objects, the following events come into play:

OBJECT	XML EVENT
Application	WorkbookBeforeXmlExport / WorkbookAfterXmlExport
Application	WorkbookBeforeXmlImport / WorkbookAfterXmlImport
Workbook	BeforeXmlExport / AfterXmlExport
Workbook	BeforeXmlImport / AfterXmlImport

The underlying XmlMap object is passed to the event procedures. In the XxxBeforeXxx events the importation or exportation can be prevented with Cancel=True .