In order to present your data (Excel file, Access database etc.) to the user in an attractive way in browser, mobile phone or PDF format, the original data must first be converted to the necessary XML formats. XML documents and data can be available in almost any structure whatsoever. Everyone can define his or her own XML document structure and describe it. However, a standard database table (Excel spreadsheet, Oracle table/view) is a plane, two-dimensional representation of data content. When you convert your data source table to XML format you get a plane column-row shaped XML output.

What to do if you need to get an especially structured XML file from your Excel source (or from other data storage)? How to create an XML with your particular multi-level tagged tree structure from your specially formatted data source?

We would first like to present a simple example showing the XML format, which XML Converter generates from an Excel source. The example is based on the document shown in Figure 2. It is an XML document consisting of the elements Cars and, in it, Car, Details and Detail. Top level of XML source tree is 'Sheet1'. It presents every 'Corporation'. The 'CARS' tag is a direct parent of elements such as 'Car', 'Details' and 'Detail'. You can see an Excel data source spreadsheet on figure 1, which is converted to the shown XML output. Also you can see this readable XML presentation, which is accompanied with an XSL stylesheet.

Figure 1. A specially structured Excel data source spreadsheet

XML Transformation Scenario is designed for transforming any data source content into a multidimensional XML documents by using XML Converter. It is a part of interface of the conversion software. This XML Converter feature provides capabilities to design and edit XML Transformation Scenario for your final structured XML document, which you get by transforming some data source. The XML Converter creates a generic template-scenario of a document. This interface provides capabilities to create an XML document template-scenario based on a specific data source content. Contents can be saved as an XML document with embedded XSD.

The XML Converter lets you create and define a template scenario for an XML document as a structured tree. This conversion scenario forms the basis for creating and editing an XML output according to the criteria defined for each data source column in the template scenario. The scenario consists of structured tags tree that is invariant and, columns and complex data representation that define the content of a final XML document.

The tags tree is made up in convenient data grid form. In a scenario background you will need to identify Tag Level of each data column, Tag Names, Sub Tag Names, Group Rows Names as a complex data representation to define structure of an XML document. See figure 3.

The XML Converter lets you create complex representation by grouping columns as a node-structured tree. The XML Converter uses a predefined template scenario to create structured XML content according to the template definition. The content can then be stored as XML. The XML Converter can also be used to create an XML DTD or XSD Schema for the document.

Figure 2. Final XML output

Sometimes users want to include XML output's columns values in specified char combinations. For example, some of fields in user's spreadsheet need to be set in a CDATA tag. XML Converter allows you to specify one attribute as being of type CDATA and have it apply to all instances of that element in the XML. For this case Scenario interface has two fields: "Prefix" and "Suffix".

Example
There is the structure of a sample Excel sheet with four columns here:

LastName
FirstName
City
Detail

What we probably need to happen is that for every record in the XML, the "Detail" attribute will be enclosed with a CDATA tag because its likely to contain HTML and other markups in it and it will be used by the Flash, which wants to see that kind of data in a CDATA tag. This is what we may get first by using XML Converter:

In Scenario interface for "Prefix" field we insert "<![CDATA[" and for "Suffix" - "]]>" strings. After reconverting we can get the following XML output:

XML Converter gives you ability to adjust and save a transformation template scenario for each converted table(spreadsheet). Once you set this template scenario for your particular data source next time you will be able to reuse your previous stored setting. How to generate a Scenario for a specially structured Excel data source by using the Converter? Our example is based on shown above Excel spreadsheet and its final XML output. Please follow the several steps:

Figure 3. XML Transformation Scenario Interface

On the "Options" form select "Use predefined XML Transformation Scenario" checkbox.

Try to create some XML output from your data source. During the creation a special "XML Transformation Scenario" shown above interface window will appear. By using this interface you will adjust and save a transformation template-scenario for each converted table.

First you will get a template for a plain table. In column 'Level' all table fields will have the same first level '>'. Values for columns 'Column Name' and 'Tag Name' will be equal. You may give new values for 'Tag Name' for every table column of your data source. During the subsequent transformation all XML tag names will be replaced with these new values.

By clicking on 'Level' column buttons for each table column you will be able generate a tree structure for your XML output. When you increase a tag level you will need to insert a name for this new tag to 'Sub Tag Name' field. Every change you make in data grid will be reflected in 'XML Structure view' window, where you can see an XML final structure representation.

'RowFlag' column helps you to identify what table columns must be included to 'rows'. What do I mean when say 'rows'? Take a look at figure 1. For one Car we have several Models, which are presented as separated rows in data source. All these rows are 'Detail' tags in our final XML. All of these rows belong to 'DETAILS' tag, which is a direct parent of these elements.

To combine all rows under the same structure level you will need to give a name for this group of rows in 'Rows Tag Name' column.

If you need you can insert particular char strings into "Prefix" and "Suffix" fields.


Only one rule. To get an XML output with a structure, which you need, your data source has to look like the sample Excel spreadsheet shown above on figure 1. By using XML Transformation Scenario interface you let know to XML Converter what a structure you would like to provide for a generated XML.

Besides creating an XML document the XML Converter allows you generate special XSL document for this final XML file. For this case you should go to the menu bar "Options" and check the question "Would you like to accompany your XML document with a template XSL style-sheet?"

Also you can select option "Fast converting mode for Excel workbooks". Excel sheets will be transformed much faster. But this way is good if you have homogeneous data for each excel sheet column. If you have deferent data types in one column (numbers, date or text) please do not check this box.
To designate the maximum width (in characters) of the text field you should enter an appropriate value in the "Width of Word document or text file in characters" box.
Converter also supports any delimited data source file (tab-delimited text file, *.csv etc). By checking "Delimited source" option on the "Options" form you let know Converter that data source is a delimited file. Symbol-delimiter is adjustable in a special textbox. You can specify not only one symbol for your source delimitation. Delimiter can be a combination of some chars. For a tab-delimited text file just insert "Tab" in the textbox.

The RustemSoft XML Converter can support non-Latin characters in XML by accepting the UTF-8 and UTF-16 encodings. People around the world can easily use it. By playing on "Unicode UTF-8 encoding" section you can adjust non-Latin characters view in your final XML.

"Encode special Low ASCII chars with HTML encoding" - by checking this box you can be able to convert some chars in your final XML. Some XML parsers need low ASCII symbols be encoded in HTML presentation. For example, ' char (apostrophe) in an XML content should have the view: &apos;

By clicking "XSL Setup" button you may call a special interface for adjusting an appearance view of your XML file along with an XSL style sheet. You can change the XSL style to what you wanted.