ExportToXml¶

Exports the data table content to XML data. For each row in the data table, a new XML child element is created at the specified XPath location.

1

$Database.ExportToXml(query: `Columns` : Array<`Expression` : string

An expression for the column, which can be a calculation or transformation.

Name : string
The name of the column to use in the results. If not specified, the

XPath : string
An XPath expression to map data to the column.>
An array of columns to include in the result set.

Map : (node: Xml) => void
A custom mapper function to apply to each node in the result set.

MaxLength : number
The maximum number of rows to return. If not set, all rows are returned.

Node : Xml
The root node for the mapping. If not specified, the global

Order : Array<Expression : string
The expression to use for ordering the results.

Type : ( ... | ... | ... )
The type of ordering (ascending or descending). If not specified, defaults to "Ascending".>
An array of order expressions to sort the results.

Parameters : object
Additional parameters to pass to the query.

Start : number
The starting index for the rows to return.

SubQueries : Array<Columns : Array<Expression : ...
An expression for the column, which can be a calculation or transformation.

Name : ...
The name of the column to use in the results. If not specified, the

XPath : ...
An XPath expression to map data to the column.>
An array of columns to include in the result set.

MaxLength : number
The maximum number of rows to return. If not set, all rows are returned.

Name : string
The name of the sub-query. The relation name can be used as the name.

Order : Array<...>
An array of order expressions to sort the results.

Parameters : object
Additional parameters to pass to the query.

Relation : string
The name of the relation to use for the sub-query.

Start : number
The starting index for the rows to return.

SubQueries : SubQuery[]
An array of sub-queries to include in the result set.

TargetSchema : string
The name of the schema to execute the query against.

TargetTable : string
The name of the table to execute the query against.

Where : Blocks : ...
An array of nested query blocks.

Condition : ...
The condition to combine this block with the next one. If not specified, defaults to "And".

Criteria : ...
An array of criteria to apply within this block.
The criteria to filter the query results.

XPath : string
The XPath expression to export the data to.>
An array of sub-queries to include in the result set.

TargetSchema : string
The name of the schema to execute the query against.

TargetTable : string
The name of the table to execute the query against.

Where : Blocks : Array<...>
An array of nested query blocks.

Condition : ( "And" | "Or" )
The condition to combine this block with the next one. If not specified, defaults to "And".

Criteria : Array<...>
An array of criteria to apply within this block.
The criteria to filter the query results.

XPath : string
The root XPath expression to map the data to.):DataTable

Parameters¶

Columns : Array<Expression : string
An expression for the column, which can be a calculation or transformation.

Name : string
The name of the column to use in the results. If not specified, the

XPath : string
An XPath expression to map data to the column.>
An array of columns to include in the result set.

Map : (node: Xml) => void
A custom mapper function to apply to each node in the result set.

MaxLength : number
The maximum number of rows to return. If not set, all rows are returned.

Node : Xml
The root node for the mapping. If not specified, the global

Order : Array<Expression : string
The expression to use for ordering the results.

Type : ( ... | ... | ... )
The type of ordering (ascending or descending). If not specified, defaults to "Ascending".>
An array of order expressions to sort the results.

Parameters : object
Additional parameters to pass to the query.

Start : number
The starting index for the rows to return.

SubQueries : Array<Columns : Array<Expression : ...
An expression for the column, which can be a calculation or transformation.

Name : ...
The name of the column to use in the results. If not specified, the

XPath : ...
An XPath expression to map data to the column.>
An array of columns to include in the result set.

MaxLength : number
The maximum number of rows to return. If not set, all rows are returned.

Name : string
The name of the sub-query. The relation name can be used as the name.

Order : Array<...>
An array of order expressions to sort the results.

Parameters : object
Additional parameters to pass to the query.

Relation : string
The name of the relation to use for the sub-query.

Start : number
The starting index for the rows to return.

SubQueries : SubQuery[]
An array of sub-queries to include in the result set.

TargetSchema : string
The name of the schema to execute the query against.

TargetTable : string
The name of the table to execute the query against.

Where : Blocks : ...
An array of nested query blocks.

Condition : ...
The condition to combine this block with the next one. If not specified, defaults to "And".

Criteria : ...
An array of criteria to apply within this block.
The criteria to filter the query results.

XPath : string
The XPath expression to export the data to.>
An array of sub-queries to include in the result set.

TargetSchema : string
The name of the schema to execute the query against.

TargetTable : string
The name of the table to execute the query against.

Where : Blocks : Array<...>
An array of nested query blocks.

Condition : ( "And" | "Or" )
The condition to combine this block with the next one. If not specified, defaults to "And".

Criteria : Array<...>
An array of criteria to apply within this block.
The criteria to filter the query results.

XPath : string
The root XPath expression to map the data to. query
    A query that defines the data to export.

Returns¶

A DataTable instance containing the exported data.

Remarks¶

When the IncludeAllColumns parameter is not specified, the default behavior is that the query returns only explicitly specified columns. If no columns are specified, the query returns all defined columns in the table.

The design of your XML data model and SQL table columns is key for ease of use. When XML node and table column names are identical, this method maps them automatically.

Map function A map function can be used to transform values before saving, for example, encrypting a password before saving it to the 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'}
    ]
});

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'
});

See Also¶

• DataTable
• $Database.ExportToXml
• $Database.ImportFromXml