$Database.ExportToXml

Overview

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

DataTable $Database.ExportToXml(query: QueryWithMappings)

Arguments

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.

$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

$Database.ExportToXml({
    TargetSchema : 'MySchema',
    TargetTable : 'Accounts',
    XPath : 'Accounts/Account',
    Map : function (accountXml) {
        this.Password = 'Enc:' + this.Password; // mark as encrypted
        this.AccountType = accountXml.Evaluate('Type/Code');  // map values whose names are not identical to SQL column names
    }
});

Example

Exporting database content into XML

// 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>

Example

Exporting with sub queries

// Export corporations with the subcorporations
$Database.ExportToXml({
    Parameters : {
        TargetSchema : 'Evrak',
        TargetTable : 'Kurum'
    },
    XPath : 'Corporations/Corporation',
    SubQueries : [
        {Name : 'SubCorporations'}
    ]
});

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.

Example

Exported XML Data

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

Example

Exporting with nested sub queries

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

Example

Exporting with XML mappings

$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

{ // Array of columns Columns : Array<QueryColumn> Map : (node: Xml) => void // Maximum number of rows. If not set all rows are returns. MaxLength : number // Root node of mapping. If not specified $Xml instance is used. Node : Xml // Array of order columns Order : Array<QueryOrder> // Additional parameters Parameters : object // Start index of rows. Start : number // Array of sub queries. SubQueries : Array<SubQuery> // Name of schema to execute query on. TargetSchema : string // Name of table to execute query on. TargetTable : string // Criteria of query Where : QueryBlock // Root xpath to be mapped. XPath : string }

QueryColumn

Defines a query column to included in result

{ // Expression of column. Expression : string // Name of column to use in results. If not specified expression is used. Name : string // XPath to be mapped. XPath : string }

QueryOrder

Defines order expression of query result

{ // Expression to order. Expression : string // Type of ordering. If not specified Ascending is used. Type : ( "Ascending" | "Descending" ) }

SubQuery

{ // Array of columns Columns : Array<QueryColumn> // Maximum number of rows. If not set all rows are returns. MaxLength : number // Name of sub query. Relation name can be used as name. Name : string // Array of order columns Order : Array<QueryOrder> // Additional parameters Parameters : object // Name of relation Relation : string // Start index of rows. Start : number // Array of sub queries. SubQueries : Array<SubQuery> // Name of schema to execute query on. TargetSchema : string // Name of table to execute query on. TargetTable : string // Criteria of query Where : QueryBlock // Specifies the target xpath to export data on. XPath : string }

QueryBlock

{ // Array of criteria blocks Blocks : Array<QueryBlock> // Condition with next block. If not specified And value is used. Condition : ( "And" | "Or" ) // Array of criteria Criteria : Array<QueryCriteria> }

QueryCriteria

Defines a criteria to be used to filter results

{ // Comparison operator. Default value is Equals. Comparison : ( "Equals" | "Different" | "LessThan" | "GreaterThan" | "LessThanOrEqualTo" | "GreaterThanOrEqualTo" | "Like" ) // Condition with next criteria. If not specified And value is used. Condition : ( "And" | "Or" ) // Criteria expression. Expression : string // Array of ignored values. IgnoredValues : any // Value or Expression to compare Value : any // Type of value. If not specified Direct value is used. ValueType : ( "Direct" | "Expression" ) }