Skip to content

ExportToXml

Exports the data table content to xml data. For each row in data table a new xml child created on specified xpath.

1
$Database.ExportToXml(query: QueryWithMappings):DataTable

Parameters

QueryWithMappings query
    Query of data to export.

Remarks

When IncludeAllColumns parameter is not specified, default behavior is query returns only explicitly specified columns. If not any column specified query returns the all defined columns on table.
The design of your XML data model and SQL table columns are the key point for the ease of use. When XML node and table column names identical, this method maps them automatically.

Info

$Database.ExportToXml, and ,$Database.ImportFromXml, methods requires a unique primary key field on database table and data model.

Map Function

Map function is used to transform values before saving. For example; encrypting a password before saving to database. See $Database.ImportFromXml for more details.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
$Database.ExportToXml({
    TargetSchema : 'MySchema',
    TargetTable : 'Accounts',
    XPath : 'Accounts/Account',
    // map values whose names are not identical to SQL column names
    Map : function (accountXml) {
        accountXml.SetValue('Password', this.Password);
        accountXml.SetValue('Type/Code', this.AccountType);
    }
});

Exporting database content into XML

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// For following xml structure
// <Root>
//    <Groups></Groups>
// </Root>
$Database.ExportToXml({
    Parameters : {
        TargetSchema : 'HR',
        TargetTable : 'Groups'
    },
    XPath : 'Groups/Group',
    Order : [
        {Name : 'Name', Type : 'Ascending'}
    ]
});
// Xml updated as;
// <Root>
//   <Groups>
//     <Group>
//        <Name>Developers (Junior)</Name>
//     </Group>
//     <Group>
//        <Name>Testers</Name>
//     </Group>
//   </Groups>
// </Root>

Exporting with sub queries

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// Export corporations with the subcorporations
$Database.ExportToXml({
    Parameters : {
        TargetSchema : 'Evrak',
        TargetTable : 'Kurum'
    },
    XPath : 'Corporations/Corporation',
    SubQueries : [
        {Name : 'SubCorporations'}
    ]
});

Info

Assume you have two SQL tables Corporations and SubCorporations and a One-To-Many relation from Corporations table to SubCorporations table which is also named SubCorporations. Also don't forget to set this relation's update rule to "Cascade".

Having defined the table columns with identical names to the XML fields, this code lets you export each corporation from SQL table into XML fields, exporting also its related SubCorporations into the SubCorporations XML nodes and resulting in the following XML data.

Exported XML Data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<GeneralDefinitions>
  <Corporations>
     <Corporation>
        <Id>9b7383a3-67ae-4fed-a135-1f981b165c43</Id>
        <Name>Corporation A</Name>
        <ShortName>CorpA</ShortName>
        <SubCorporations>
           <SubCorporation>
              <Id>309069b4-4274-4063-b7ad-75c5657d7474</Id>
              <Name>SubCorporation A 1</Name>
              <ShortName>SubCorpA1</ShortName>
           </SubCorporation>
           <SubCorporation>
              <Id>6ba3cc79-9ff7-4232-8f61-9245a38fd2bb</Id>
              <Name>SubCorporation A 2</Name>
              <ShortName>SubCorpA2</ShortName>
           </SubCorporation>
        </SubCorporations>
     </Corporation>
     <Corporation>
        <Id>5a900112-a869-41cf-931c-7379c1df518e</Id>
        <Name>Corporation B</Name>
        <ShortName>CorpB</ShortName>
        <SubCorporations>
           <SubCorporation>
              <Id>8e7ca6d2-5629-4f87-82ed-73c72cfb171e</Id>
              <Name>SubCorporation B 1</Name>
              <ShortName>SubCorpB1</ShortName>
           </SubCorporation>
           <SubCorporation>
              <Id>7c5f330e-68d8-45f0-800e-04e2f7e035e0</Id>
              <Name>SubCorporation B 2</Name>
              <ShortName>SubCorpB2</ShortName>
           </SubCorporation>
        </SubCorporations>
     </Corporation>
  </Corporations>
</GeneralDefinitions>

Exporting with nested sub queries

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
$Database.ExportToXml({
    Parameters : {
        TargetSchema : 'Contract',
        TargetTable : 'Events'
    },
    Where : {
        Criteria : [
            {Name : 'ReminderDate', Value : $Calendar.Today, Comparison : 'LessThan'},
            {Name : 'Durum', Value : 'IPT', Comparison : 'Different'},
            {Name : 'Type', Value : 'R'},
            {Name : 'Status', Value : 'W'}
        ]
    },
    SubQueries : [
        {
            Name : 'Contract',
            Parameters : {
                IncludeAllColumns : 'True'
            },
            SubQueries : [
                {
                    Name : 'Party'
                },
                {
                    Name : 'Versions'
                },
                {
                    Name : 'Events'
                }
            ]
        }
    ],
    XPath : 'ContractEvent',
    Order : [
        {Name : 'ReminderDate', Type : 'Descending'}
    ]
});

Exporting with XML mappings

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
$Database.ExportToXml({
    Parameters : {
        TargetSchema : 'HR',
        TargetTable : 'OrganizationUnitPositionMembers',
        IncludeAllColumns : 'True'
    },
    Columns : [
        {
            Name : 'Employee',
            Properties : {
                XPath : 'Id'
            }
        },
        {
            Name : 'Employee.Person.DisplayName',
            Properties : {
                XPath : 'Name'
            }
        },
        {
            Name : 'OrganizationUnitPosition.Organization',
            Properties : {
                XPath : 'Department'
            }
        },
        {
            Name : 'OrganizationUnitPosition.Organization.Name',
            Properties : {
                XPath : 'Department/@Name'
            }
        }
    ],
    Where : {
        Criteria : [
            {
                Name : 'OrganizationUnitPosition.Manager',
                Value : 'D7B70176-C44D-44BB-A8C0-7900BC5DAF2A',
                Condition : 'And'
            },
            {Name : 'Employee.User.Disabled', Value : true, Comparison : 'Different', Condition : 'And'}
        ]
    },
    XPath : 'EmployeeList/Employee'
});

Types

QueryWithMappings

Columns : Array<QueryColumn>
Array of columns

Map : (node: Xml) => void

MaxLength : number
Maximum number of rows. If not set all rows are returns.

Node : Xml
Root node of mapping. If not specified $Xml instance is used.

Order : Array<QueryOrder>
Array of order columns

Parameters : object
Additional parameters

Start : number
Start index of rows.

SubQueries : Array<SubQuery>
Array of sub queries.

TargetSchema : string
Name of schema to execute query on.

TargetTable : string
Name of table to execute query on.

Where : QueryBlock
Criteria of query

XPath : string
Root xpath to be mapped.

QueryColumn

Defines a query column to included in result

Expression : string
Expression of column.

Name : string
Name of column to use in results. If not specified expression is used.

XPath : string
XPath to be mapped.

QueryOrder

Defines order expression of query result

Expression : string
Expression to order.

Type : ( "Ascending" | "Descending" )
Type of ordering. If not specified Ascending is used.

SubQuery

Columns : Array<QueryColumn>
Array of columns

MaxLength : number
Maximum number of rows. If not set all rows are returns.

Name : string
Name of sub query. Relation name can be used as name.

Order : Array<QueryOrder>
Array of order columns

Parameters : object
Additional parameters

Relation : string
Name of relation

Start : number
Start index of rows.

SubQueries : Array<SubQuery>
Array of sub queries.

TargetSchema : string
Name of schema to execute query on.

TargetTable : string
Name of table to execute query on.

Where : QueryBlock
Criteria of query

XPath : string
Specifies the target xpath to export data on.

QueryBlock

Blocks : Array<QueryBlock>
Array of criteria blocks

Condition : ( "And" | "Or" )
Condition with next block. If not specified And value is used.

Criteria : Array<QueryCriteria>
Array of criteria

QueryCriteria

Defines a criteria to be used to filter results

Comparison : ( "Equals" | "Different" | "LessThan" | "GreaterThan" | "LessThanOrEqualTo" | "GreaterThanOrEqualTo" | "Like" )
Comparison operator. Default value is Equals.

Condition : ( "And" | "Or" )
Condition with next criteria. If not specified And value is used.

Expression : string
Criteria expression.

IgnoredValues : any
Array of ignored values.

Value : any
Value or Expression to compare

ValueType : ( "Direct" | "Expression" )
Type of value. If not specified Direct value is used.

See Also