Conversion to XML format. A brief description of creating an XML file using an XSD schema using the example of creating a download of Statistical Reporting for the Federal Customs Service
I have been working with XML files for a long time, but only now I asked myself: How regular means create and edit XML files in MS Excel? Excel is a convenient and intuitive tool for creating, editing and processing various data. Excel is studied in school and, probably, there is not a person who knows how to work on a PC and does not know the basics of working in Excel. Therefore, in my opinion, this is the most suitable tool that can be recommended ordinary users to create and edit data files for various applications, including Web applications. I have MS Excel 2013 installed at home and using its example I will describe the simple procedures that need to be performed when creating an XML data file in Excel. I think that in more earlier versions Excel will work too. To create an XML data file, let's take a few simple steps. Let's look at them using the example of creating an event data file.
1. First, you need to create and fill out a data table in Excel in accordance with the data structure. Please use the menu item for this Insert/Table.
2. Let's create an XML file schema. To do this, enter the following lines in a text editor and save them in a file.
XHTML
|
3. Now open the tab Developer, press the button Source and specify the XML source file. If necessary, enable the menu item Developer in Excel Settings.
4. It remains to compare the elements of the diagram and the columns of the table by dragging the elements to the corresponding column headings.
5. And last step: Export table data to an XML file using the context menu. Stand on any table cell, right-click, select XML/Export… and save the XML file with the desired name.
Save the Excel file for later editing and addition. Subsequently, paragraphs 1.-4. there will be no need to do this!
P.S. Why did I write this banal note? Firstly, so as not to forget, and secondly, I think that this information will be useful to users, starting with the next version 0.6 ;-)
The XML format is designed to store data that may be useful in the operation of certain programs, websites and support for certain markup languages. It is not difficult to create and open a file with this format. This can be done even if you do not have any specialized software installed on your computer.
XML itself is a markup language, somewhat similar to HTML, that is used on web pages. But if the latter is used only to display information and its correct marking, then XML allows it to be structured in a certain way, which makes this language somewhat similar to an analogue of a database that does not require a DBMS.
You can create XML files using specialized programs or built-in Windows text editor. The ease of writing code and the level of its functionality depend on the type of software used.
Method 1: Visual Studio
Instead of this Microsoft code editor, you can use any of its analogues from other developers. In fact Visual Studio is a more advanced version of the usual "Notepad". The code now has special highlighting, errors are highlighted or corrected automatically, and special templates are already loaded into the program that make it easier to create large XML files.
To get started you need to create a file. Click on an item "File" V top panel and from the drop down menu select "Create…". A list will open indicating the item "File".
The newly created file will already have the first line with the encoding and version. By default, the first version and encoding are specified UTF-8, which you can change at any time. Next, to create a full-fledged XML file, you need to write down everything that was in the previous instructions.
When finished, select again in the top panel "File", and there is an item from the drop-down menu "Save everything".
Method 2: Microsoft Excel
You can create an XML file without writing code, for example, using modern versions, which allows you to save tables with this extension. However, you need to understand that in this case it will not be possible to create something more functional than a regular table.
This method is more suitable for those who do not want or do not know how to work with code. However, in this case the user may encounter certain problems when rewriting a file in XML format. Unfortunately, the operation of converting a regular table to XML is possible only on the newest versions of MS Excel. To do this, use the following step-by-step instructions:
Method 3: Notepad
Even a regular one is quite suitable for working with XML "Notebook", however, it will be difficult for a user who is not familiar with the syntax of the language, since it needs to be written in various commands and tags. The process will be somewhat simpler and much more productive in specialized programs for editing code, for example, in Microsoft Visual Studio. They have special tag highlighting and tooltips, which greatly simplifies the work of a person new to the syntax of this language.
This method does not require downloading anything, since operating system already built in "Notebook". Let's try to make a simple XML table in it using these instructions:
Your finished result should look something like this:
XML compilers must process this code as a table with one column containing data about a certain Ivan Ivanov.
IN "Notepad" it is quite possible to make simple tables like this, but when creating larger data sets, difficulties may arise, since in ordinary "Notepad" There are no functions for correcting errors in the code or highlighting them.
As you can see, there is nothing complicated in creating an XML file. If desired, it can be created by any user who more or less knows how to work on a computer. However, to create a full-fledged XML file, it is recommended to study this markup language, at least at a primitive level.
If you need to create an XML data file and an XML schema file from a range of cells in a worksheet, you can use version 1.1 of the XML Tools for Excel 2003 add-in to extend the existing XML capabilities in Microsoft Excel 2007 and later versions.
Note: This add-in was developed for Excel 2003. The documentation and user interface refer to lists, which are called Excel tables in versions of the application later than Excel 2003.
For more information about working with this add-in, see Use the XML Tools add-in version 1.1 for Excel 2003.
Step 2: Convert a range of cells to an XML table
Click the button OK.
In the highlighted line in the VBA code module, remove "50" from the line. In other words, the change:
XMLDoc as msxml2 . DOMDocument50
To whom:
XMLDoc how MSXML2 is described. DOMDocumentPress F5 to search next line containing the text "XMLDoc As msxml2.DOMDocument50", click the button OK and change the line as in the previous paragraph.
Press F5 again to find and change other instances of the line.
If you no longer see the VBA error message after you press F5, close the Visual Basic Editor to return to the workbook. The range of cells will be converted to an XML table.
Note: To display all XML maps in a workbook, in the tab Developer in Group XML click the button Source to display the XML Source task pane. At the bottom of the XML Source task pane, click XML Maps.
If the tab Developer is not visible, follow the first three steps in the next section to add it to the Excel ribbon.
Enter the data for which you want to create an XML data file and an XML schema file. The data must be presented in a tabular format in the form of columns and rows (called ordinary data).
On the tab Add-ons in Group Menu commands click the arrow next to the caption Facilities and then click the button Convert range to XML list.
Enter the range of cells with the data you want to convert as an absolute reference in the text box.
In field The first line contains the column names select No, if the first row contains data, or Yes if the first row contains column headers, and click OK.
Excel will automatically create the XML schema, link the cells to the schema, and create the XML table.
Important: If the editor opens Visual Basic and a Visual error message will be displayed Basic for Applications (VBA), follow these steps.
Step 3: Export the XML table to an XML data (XML) file
Note: When creating XML maps and exporting data in Excel to XML files, there is a limit to the number of rows that can be exported. When exporting to an XML file from Excel, you can save up to 65,536 rows. If the file contains more than 65,536 rows, Excel will only be able to export the first rows (number of rows mod 65,537). For example, if a worksheet contains 70,000 rows, Excel exports 4,464 rows (70,000 mod 65,537). We recommend following one of the following tips: 1) use the XLSX format; 2) save the file in "XML 2003 Table (*.xml)" format (this will lose the mappings); 3) delete all lines after 65536 and then export again (this will keep the mappings but will lose the lines at the end of the file).
I would like to talk in one place about a very typical task when working with XML, namely, about the creation XML based on the existing one. This operation will consist of several stages, during which the decisions will be made additional tasks, such as:
- Creation XML based XSD, and vice versa.
- Serialization and deserialization of objects.
- Creating a class for future serialization from XML or XSD.
XSD is a language for describing XML. And if you look at the contents of the file containing this description, the question will arise, why describe the seemingly obvious tags of your XML? But imagine that you are integrating with some external system, and you want to use the format for data exchange XML. So, XSD allows you to describe for each element its name, possible attributes, mandatory fields or attributes, and make it clear that the contents of a tag can contain only one internal element ( ) and no more, or a node may have a sequence of elements (< a> ... ). When external system is yours, then problems may not arise, but when it outside organization, Then this description through XSD, will be a tool for resolving disputes with the format, and also, using utilities, you can check the compliance of any XML original format.So, what are we going to do to learn how to stamp XML based on your standard? I will describe everything in order.
Available:
Some kind of XML (xmlfile.xml), saved in text format to file. Below is the text:
<
catalog
>
<
book
id
=
"
bk101
"
>
<
author
>Gambardella, Matthew
<
title
>XML Developer's Guide
<
genre
>Computer
<
price
>
44.95
<
publish_date
>
2000-10-01
<
description
>An in-depth look at creating applications with XML.
<
book
id
=
"
bk102
"
>
<
author
>Ralls, Kim
<
title
>Midnight Rain
<
genre
>Fantasy
<
price
>
5.95
<
publish_date
>
2000-12-16
<
description
>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.
Steps to create a new one:
- First using the utility XSD.exe, or using online services(search for "xml to xsd" one of them www.freeformatter.com/xsd-generator.html), we will create from the existing XML its primitive XSD description. At the output we will have a completely, automatically created output.xsd a file that is already usable but may not be complete.
- Next, based on output.xsd , classes will be created in C# xmlclass.cs. After which, we add it to our project. You can create a class using XSD.exe or xsd2code utility, or any online service.
- Now we are ready to use the file classes xmlclass.cs. Therefore, let’s create and fill instances with data xmlclass, and then, using the serialization process, we will convert objects into XML line, and then save it in new file. Thus we get XML based on what is available.
Step 1: Generate XSD from XML.
So, here are the methods that I use myself:- Utility xsd.exe- creator Microsoft.
- Utility xsd2code.exe - third party application, which can do everything the same as xsd.exe, but is available for download directly (at this moment became paid from Trial period).
- Use of any online services.
Link to download and install one of SDK:
Windows SDK for Windows 7 and .NET Framework 4.
Let's use the search and find the file xsd.exe, after which (to make our work easier), copy it to the folder created in the root of the disk C:\xmltoxsd. Next, we copy our xmlfile.xml and everything is ready to start.
We tear off " Command line"(Start -> "Run" -> "cmd"), go to our directory and call with default parameters xsd.exe, passing our xmlfile.xml as a parameter:
And we see that a file has appeared next to it xmlfile.xsd with content.
That's all! These actions are enough to create a primitive XSD file to work with. But it is worth understanding that xsd.exe, like any other utility, knows nothing about the types of your fields (so the field type will almost always be string), as well as about all variations of attributes and parameters. That is, if some tag does not have an attribute in your XML, then it won’t be in the description. That's why XML should be the most complete and if the element may contain several child fields, it is better to add at least two, so that xsd.exe I realized that this is a collection.
Syntax XSD not very complicated, so if you are writing a fairly serious description, you may have to work with a file.
Step 2: Create a Class from XSD.
To create a class in C#, we will need to use again xsd.exe but with a different parameter /classes, passing the path to our xmlfile.xsd.
After execution, the xmlfile.cs file will appear, the contents of which are shown below in compressed form:
As you can see, for the inner element catalog a class was created catalogBook, whose fields correspond to the fields in XML, and the field ID marked as an attribute. Xsd.exe I correctly understood what was inside catalog a collection of books is stored, so the property appeared Items, to populate the child collection.
Comment: xsd.exe cannot add comments to the resulting classes and fields. In description XSD there is an element called< xs:annotation > , which most often contains< xs:documentation >, inside which is a description of the field. In theory, it should be placed in the /// description, but at the moment xsd.exe refuses to add text to the description, so you have to resort to alternatives such as xsd2code or online services.
Step 3: Serialize objects to XML.
The resulting classes are in the file xmlfile.cs. Next, add its contents or the file itself to the project Visual Studio, after which you can work with it. I will create demo book objects and then save them to a file in the format XML.Private void Example() ( // Create the first book var book1 = new catalogBook () ( author = "King" , description = "Very interesting book" , genre = "Fantasy" , price = 22.ToString(), id = "42011" , title = "It"
};
!} // Create a second book var book2 = new catalogBook () ( author = "O"Brien, Tim" , description = "Microsoft's .NET initiative is explored in detail in this deep programmer"s reference.", genre = "Computer" , price = 36.ToString(), id = "30012" , title = "Microsoft .NET: The Programming Bible"
};
// Create a directory root element containing the two books above var catalog = new catalog() (Items = new(book1, book2)); // Contains XML object catalog var xmlCatalog = Serialize(catalog); // Write a string to a file
//
TODO Made for demonstration purposes. It is advisable to call the Serialize method to transfer the Stream to the file File .WriteAllText("Output.xml" , xmlCatalog); ) private string Serialize
you can see that we saved it the way we wanted (Bottom file - " Created by code:
- Let's look at the procedure for converting a database object into XML format In the database window, select the required object by left-clicking and select the command File, Export(File, Export) or click on the object right click and select in context menu team
- Export (Export). A dialog box will appear Export an object(Export To). In the dropdown list Types(Save as type) select the element XML Documents(XML Documents). In field File name(File name) enter a name context menu team
- Export XML export(Export XML) (Fig. 12.9).
- This dialog box contains three checkboxes for the three components of an XML document: data, data schema, and data presentation. (For more information, see the “XML Format in Microsoft Office 2002” section of this chapter). Select the check boxes for the parts of the XML document that you want to export. For example, if you select the checkboxes simultaneously data (XML)(Data (XML)) and data schema(Schema of the data), the data schema will be exported and saved separately from the XML data file, in an XSD file. For tables, you can export only the data or its structure, or the data and structure together and not export the table view as a Web page: checkbox data presentation(XSL) (Presentation of your data (XSL)) can be removed.
Rice. 12.9. Dialog window XML export
Comment
The Data (XML) check box should only be cleared if you only want to export a new representation of the database object and the data has previously been exported to an XML file. Otherwise, when you open the resulting Web page, you will receive a message about an error connecting to the data source, and the page itself will be almost empty.
- to install Extra options conversion, click the button Additionally(Advanced) (Fig. 12.10).
Rice. 12.10. Tab Data dialog box XML export
- In addition, this dialog box contains two more tabs for the two remaining components of the XML document: data schema and data representation. On each of these tabs, you can select a checkbox that allows you to export the corresponding part of the XML document, specify the path and name of the file in which this part of the document will be saved, and set some additional parameters:
- on the tab Data(Data) using dropdown list Encoding(Encoding) you can select the encoding to save the page contents;
- on the tab Scheme(Schema) you can use the switches to select whether to export the data structure to separate file XSD or embed it in an XML file along with the data;
Rice. 12.11. Account report opened in the database
- on the tab Presentation(Presentation) using the switches, you can select the presentation format of the Web page: HTML processed on the client side or ASP server side. If the exported object contains pictures that you want to include in the resulting document, you can specify the name of the folder in which they are located. On the same tab there is a field for entering the name of the XSL file with a description of the page design. If specified file does not exist, Access 2002 will create an XSL file describing the external representation of the document (the developer can later use this as the basis for developing an XSLT file to convert the XML document to another format).
If required Additional Information about representing data in XML format, click the button Reference(Help). Information about developing and converting XML documents can be found on the Microsoft Developer Web site: http://msdn.microsoft.com/.
Rice. 12.12. The "Invoice" report, the data of which is converted into XML format, opened in Internet Explorer
- Click OK.
- If the proposed transformation parameters have not been changed, three files will be created as a result: XML - with data, XSL - with styles, and HTM - with a representation of the converted object. To open the received Web page, you need to open it on the Internet Explorer file HTM or ASP, depending on which presentation format was chosen..
Unfortunately, subforms and reports, and associated graphics will not be added to XML document, obtained from a database object. However, simple objects with embedded graphics exported to XML will look almost exactly the same as the original ones in Access. You can verify this by comparing, for example, the original and the Invoice report converted to XML format from the Northwind database (Figures 12.11 and 12.12).
If there is a lot of data in the data source of the selected object, loading the Web page into the Internet browser may take a significant amount of time.
Comment
Since the data itself contained in such Web document, are stored entirely in an XML file, there is no need to access the database from which the data was originally taken (as, for example, in the case of converting a report to XML format). This means that to provide access to such a document, it is enough to place only the mentioned set of files containing the description of this document, on Web server. On the other hand, this means that changing the data in such a document is only possible using applications that have access to the XML file and are able to perceive this format.