SQL Server to XML: How to transfer data from SQL Server to XML File
Step
1. Create a new job, and open the [Design Job] interface.
2. Create a new MSSQL dataset as source in [Design Job] interface, and open the property dialog of MSSQL dataset, set MSSQL connection parameters in [Database] sheet, then click [Import field definition] button in [Fields] sheet to import field definition information.
Following is the detailed parameters of MSSQL should be setted.
Property name
Description
Host
The host name or DNS name of SQL Server server.
Database
Database name of SQL Server server.
User name
It is used to connect the SQL Server database.
Password
Connecting password of the SQL Server database.
Table name
Once you set up the parameters for database connection, click the combo box, all tables will be listed.
Field
List the definition information of the field that has the above format.
Before reading source data, you must define fieldĄ¯s information. About how to get field information.
3. Create a new Local file dataset as target in [Design Job] interface,
and open the property dialog of Local file dataset, set table type as "XML file" in [General] sheet, and set other parameter,
then click [Import field definition] button in [Fields] sheet to import field definition information.
Following is the most important parameters of Local file should be setted.
Property name
Description
XML format
Specify what format of XML-file will be generated when execute method is called.
Use the property to indicate what XML file you want to generate. These are the following possible values for mode:
xmlIE:XML-file which all rows are aligned using spaces, so you can view this file in any text editor in "tree"-mode. This mode is optimized for viewing in MS Internet Explorer browser.
xmlIECompact: similar to xmlIE but total number of spaces is limited.
xmlClientDataset:XML-file which is compatible with TClientDataset component in Delphi. So you can load this file using ClientDataset.LoadFromFile method in Delphi.
4. Connect source and target. Create a new DBLink between MSSQL dataset and Local File dataset, and open the property dialog of DBLink.Be careful that only "Append records" item is valid in "update mode" parameter.
5. Mapping fields. Click [Field mapping] button in [General] sheet in property dialog of DBLink to open [Set DBLink Fields Mapping] interface. You can edit calculation expression for each target fields, or drop source field to target field by mouse to generate field mapping list.
