Skip to content

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: QueryWithMappings):DataTable

Parameters

QueryWithMappings 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.

Info

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

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

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

Defines a query with mappings to an XML structure.

Columns : Array<QueryColumn>
An array of columns to include in the result set.

Map : (node: Xml) => void

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

Order : Array<QueryOrder>
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<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 : QueryBlock
The criteria to filter the query results.

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

QueryColumn

Defines a query column to be included in the result set.

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.

QueryOrder

Defines the ordering expression for the query results.

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

Type : ( "Ascending" | "Descending" )
The type of ordering (ascending or descending). If not specified, defaults to "Ascending".

SubQuery

Defines a sub-query to include within a main query.

Columns : Array<QueryColumn>
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<QueryOrder>
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 : Array<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 : QueryBlock
The criteria to filter the query results.

XPath : string
The XPath expression to export the data to.

QueryBlock

Defines a block of criteria to group conditions within a query.

Blocks : Array<QueryBlock>
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<QueryCriteria>
An array of criteria to apply within this block.

QueryCriteria

Defines a criteria to filter the query results.

Comparison : ( "Equals" | "Different" | "LessThan" | "GreaterThan" | "LessThanOrEqualTo" | "GreaterThanOrEqualTo" | "Like" )
The comparison operator to use. Defaults to "Equals".

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

Expression : string
An expression for the criteria.

IgnoredValues : any
An array of values to ignore.

Value : any
The value or expression to compare against.

ValueType : ( "Direct" | "Expression" )
The type of the

See Also