Page Comparison

...

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.

...

Code Block

language	js

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

Example

Exporting database content into XML

...

Code Block

language	js

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

Example

Exported XML Data

Code Block

language	js

<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

Code Block

language	js

$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

Code Block

language	js

$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: XmlNode) => 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 : XmlNode // 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

...

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

...

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

...

{ // 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" ) }

Examples

$Database.ExportToXml({ TargetSchema : 'MySchema', TargetTable : 'Accounts', XPath : 'Accounts/Account', Map : function (accountXml) {

Code Block

language	js

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.

Example

Exported XML Data

Code Block

language	js

<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>
         this.Password = 'Enc:' + this.Password; // mark as encrypted <Id>8e7ca6d2-5629-4f87-82ed-73c72cfb171e</Id>
              this.AccountType<Name>SubCorporation = accountXml.Evaluate('Type/Code');  // map values whose names are not identical to SQL column namesB 1</Name>
              <ShortName>SubCorpB1</ShortName>
     } });

...

Code Block

language	js

// For following xml structure <//SubCorporation>
    <Root> //    <Groups></Groups> // </Root>
$Database.ExportToXml({<SubCorporation>
     Parameters : {       <Id>7c5f330e-68d8-45f0-800e-04e2f7e035e0</Id>
 TargetSchema : 'HR',         TargetTable : 'Groups'<Name>SubCorporation B 2</Name>
  },     XPath : 'Groups/Group',     Order<ShortName>SubCorpB2</ShortName>
: [         {Name : 'Name', Type : 'Ascending'}</SubCorporation>
        ]
});
// Xml updated as;
// <Root>
//   <Groups>
//     <Group>
//</SubCorporations>
     </Corporation>
  </Corporations>
</GeneralDefinitions>

Example

Exporting with nested sub queries

Code Block

language	js

$Database.ExportToXml({
    Parameters : {
        TargetSchema : <Name>Developers (Junior)</Name>
//'Contract',
      </Group> // TargetTable : 'Events'
 <Group> //  },
    Where <Name>Testers</Name>
//: {
    </Group> //   </Groups>
// </Root>

...

Code Block

language	js

// Export corporations with the subcorporations
Database.ExportToXml({Criteria : [
          Parameters : {Name         TargetSchema: 'ReminderDate', Value : $Calendar.Today, Comparison : 'EvrakLessThan'},
        TargetTable : 'Kurum'  {Name :  }'Durum', Value :   XPath'IPT', Comparison : 'Corporations/CorporationDifferent'},
    SubQueries : [      {Name :  {Name'Type', Value : 'SubCorporationsR'},
    ] });

...

Code Block

language	js

<GeneralDefinitions>   <Corporations>    {Name  <Corporation>
: 'Status', Value : 'W'}
       <Id>9b7383a3-67ae-4fed-a135-1f981b165c43</Id>
 ]
    },
  <Name>Corporation A</Name> SubQueries : [
     <ShortName>CorpA</ShortName>   {
     <SubCorporations>       Name : 'Contract',
   <SubCorporation>         Parameters : {
   <Id>309069b4-4274-4063-b7ad-75c5657d7474</Id>             IncludeAllColumns : <Name>SubCorporation'True'
 A 1</Name>          },
    <ShortName>SubCorpA1</ShortName>        SubQueries : [
 </SubCorporation>            <SubCorporation>   {
           <Id>6ba3cc79-9ff7-4232-8f61-9245a38fd2bb</Id>         Name : 'Party'
   <Name>SubCorporation A 2</Name>           },
   <ShortName>SubCorpA2</ShortName>            </SubCorporation> {
       </SubCorporations>      </Corporation>      <Corporation> Name : 'Versions'
     <Id>5a900112-a869-41cf-931c-7379c1df518e</Id>         <Name>Corporation B</Name> },
       <ShortName>CorpB</ShortName>         <SubCorporations>{
           <SubCorporation>         Name : 'Events'
   <Id>8e7ca6d2-5629-4f87-82ed-73c72cfb171e</Id>             }
 <Name>SubCorporation B 1</Name>         ]
     <ShortName>SubCorpB1</ShortName>   }
    ],
   </SubCorporation> XPath : 'ContractEvent',
    Order : [
 <SubCorporation>       {Name : 'ReminderDate', Type : 'Descending'}
  <Id>7c5f330e-68d8-45f0-800e-04e2f7e035e0</Id>  ]
});

Example

Exporting with XML mappings

Code Block

language	js

$Database.ExportToXml({
    Parameters : {
    <Name>SubCorporation B 2</Name>  TargetSchema : 'HR',
        TargetTable  <ShortName>SubCorpB2</ShortName>: 'OrganizationUnitPositionMembers',
        IncludeAllColumns :  </SubCorporation>'True'
    },
    Columns </SubCorporations>: [
    </Corporation>   </Corporations> </GeneralDefinitions>

...

Code Block

language	js

$Database.ExportToXml({
 
  Parameters : {        Name TargetSchema : 'ContractEmployee',
        TargetTable : 'Events'  Properties : {
},     Where : {         CriteriaXPath : ['Id'
            }
{Name : 'ReminderDate', Value : $Calendar.Today, Comparison : 'LessThan'},
        {
      {Name : 'Durum', Value : 'IPT', ComparisonName : 'DifferentEmployee.Person.DisplayName'},
            {Name : 'Type', ValueProperties : 'R'},{
             {Name : 'Status', ValueXPath : 'WName'}
         ]   }
 },     SubQueries : [},
        {
            Name : 'ContractOrganizationUnitPosition.Organization',
            ParametersProperties : {
                IncludeAllColumnsXPath : 'TrueDepartment'
            }
        },
        {
       SubQueries     Name : [
'OrganizationUnitPosition.Organization.Name',
            Properties :  {
                XPath    Name : 'PartyDepartment/@Name'
            }
   },     }
    ],
    Where : {
        Criteria : [
         Name : 'Versions' {
               }, Name : 'OrganizationUnitPosition.Manager',
             {   Value             : 'D7B70176-C44D-44BB-A8C0-7900BC5DAF2A',
    Name : 'Events'          Condition : 'And'
    }        },
    ]        {Name }
    ],
    XPath : 'ContractEvent',: 'Employee.User.Disabled', Value : true, Comparison : 'Different', Condition : 'And'}
    Order : [  ]
    },
 {Name : 'ReminderDate', TypeXPath : 'Descending'}
    ]
EmployeeList/Employee'
});

...

language	js

...

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

...

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

...

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

...

{ // 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" ) }

Versions Compared

Old Version 4

New Version Current

Key

Map Function

Example

Example

Example

Example

Types

QueryWithMappings

QueryColumn

QueryOrder

SubQuery

QueryBlock

QueryCriteria

Examples

Example

Example

Example

Types

QueryWithMappings

QueryColumn

QueryOrder

SubQuery

QueryBlock

QueryCriteria