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 the Corporations table to the SubCorporations table, also named SubCorporations. Also, do not 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'
});

See Also