Using Excel to Report on XIN Files (as XML) Excel has built in support for reading and displaying XML data. This allows it to be used to directly read InRoads XIN data. Actually, the big three office products can read XML files, but the hands down best of these, is Excel. Before you get too excited, there are limitations to this capability. The biggest hurdle is volume of data. An XIN file simply has more places to store data than Excel can handle. This document was prepared using Excel 2003, but if I am not mistaken, even Excel 2007 cannot handle a complete XIN file. The example used herein will be using an ImportRuleSetCollection.

Step 1 – Defining the XML Map To begin this process, once a new workbook is open, go to the Data XML XML Source… menu item to open the XML Source task pane. This will initially be blank. Click on the XML Maps button. This will open the XML Maps dialog box.

This figure shows the dialog box after using the Add… button to browse to and select an XIN file. It will be necessary to change the Files of type: filter to All Files (*.*) to see the target XIN file. Upon accepting the browsed to XIN file, another dialog box will open. It is mostly informational. It explains that Excel cannot determine a schema for the file and will therefore generate one automatically. There may be a way to direct Excel to the proper schemas, but I have not found how to do this, nor have I found it necessary. Press the OK button.

After selecting the XIN file, the XML Maps dialog box can also be dismissed by pressing its OK button.

K:\AB_CAD_STND\Documentation\Bentley\InRoads\Using Excel to Read the XML Data in an XIN File.doc Page 1 of 5

Using Excel to Report on XIN Files (as XML) At this point the XML Source task pane will display the contents of the XIN file in a manner similar to the RAW XML style sheet of the Report Browser. It will have the “plus/minus” icons next to each node of the XIN file and can be collapsed and expanded by clicking on them to toggle between expanded and collapsed.

Locating the ImportRuleSetCollection Node To bring the desired subset of the XIN into Excel, one must collapse many nodes or scroll down to locate the ImportRuleSetCollection node. It is tedious, but in the long run far easier to collapse the unwanted nodes than it is to locate the desired node while scrolling down the task pane. The only method I have found that can speed this up is to get focus into the XML Source task pane and to use the down arrow key to move to each top node and then press the minus key. By alternating down arrow and minus keys repeatedly, one can quickly reduce the XIN tree display to show the top note of each major section – the “collections” as they are named. With the task pane tree display sufficiently collapsed, scroll up or down until you locate the ImportRuleSetCollection node.

K:\AB_CAD_STND\Documentation\Bentley\InRoads\Using Excel to Read the XML Data in an XIN File.doc Page 2 of 5

Using Excel to Report on XIN Files (as XML) Clicking on the plus sign will expand it – This figure shows it expanded and selected.

K:\AB_CAD_STND\Documentation\Bentley\InRoads\Using Excel to Read the XML Data in an XIN File.doc Page 3 of 5

Using Excel to Report on XIN Files (as XML) As you can see, even this one node tree contains many branches. You can also see that Excel displays the initial record of this tree. To see more data, the node must be added to a worksheet.

Mapping an Element The next step is to map an element into a location is the spreadsheet. The easiest way to do this is to right click on the highlighted element in the XML Source task pane. A small menu will open – select Map element…

This will open another dialog box for you to identify the location to map the elements. Unless you have a reason, you can select the default location by pressing the OK button. This will map the header row and assign AutoFilters to them. It will look something like this.

Note that it preserves the Attribute names from the XIN file and appends an index number to duplicates. Is this case, the Item Name has a Description and the Name has a Description, so the second Description becomes Description2. Note too that there is a blue asterisk “*” in the cell A2. This is where the XML data will begin filling in, in the next step.

Displaying the XML Data The final step is to display the XML data. This is done using the Refresh XML Data tool. There are a number of methods to activate that tool. One the Data menu, using XML Refresh XML Data. You can right click in any of the cells in the XML area of the spreadsheet. The popup menu that opens will have an XML item and it will have a Refresh XML Data submenu. Or you can use the List toolbar shown in the next figure.

K:\AB_CAD_STND\Documentation\Bentley\InRoads\Using Excel to Read the XML Data in an XIN File.doc Page 4 of 5

Using Excel to Report on XIN Files (as XML)

The icon will be grayed out if the active cell of the spreadsheet is not in the XML Data area. Running the command will result in the rows being populated with the values from the XML file. In this figure, you can see two rules of a much larger ruleset and you can see some of the limitations of this process. (Note the headers were turned 90° to allow narrow columns when possible.)

The first rule uses three lines to describe itself while the next rule only uses two. This is due to the heavy use of Nodes with Attributes in the XIN file. Many XML files use Nodes with values and no Attributes. The XIN file does not use any nodes with values. When displayed using Excel, only the Attribute names and Attribute values are displayed. Notice, there is no field for the ImportRuleSetCollection node, the ImportRuleSet node, the Rule node, the Level, Linestyle, Type or CellName nodes. If you want to see these in Excel, you have to insert one or more rows above the row headers and manually add them yourself. Because of this, when there are multiple rule criteria, there are multiple rows. The TO-BC rule uses Level, Color and LineStyle so it uses three lines. The TO-BLDG rule only uses Level and Color so it uses two lines. These criteria are Nodes below the Rule node – if any of these have values, they generate another row. And if more than one value is stored for any of these nodes, the XIN file contains multiple nodes and there will be one row for each node.

Conclusions Writing XSL Stylesheets is not a trivial task, but will give you more control over the outcome than Excel. But in a pinch, there is no faster way to get XIN data into an easy to follow format.

K:\AB_CAD_STND\Documentation\Bentley\InRoads\Using Excel to Read the XML Data in an XIN File.doc Page 5 of 5