$Database.ImportFromXml

Overview

Imports xml data to specified table. Please see DataTable.ImportFromXml for more details.

DataTable $Database.ImportFromXml(query: QueryWithMappings)

Arguments

Query object to import

Remarks

This method is a wrapper method for DataTable.ImportFromXml and DataTable.Save method.

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.

Map Function

Map function is used to transform values before saving. For example; encrypting a password before saving to database. See $Database.ExportToXml for more details.

Example

$Database.ImportFromXml({
    TargetSchema : 'Edoksis',
    TargetTable : 'Accounts',
    XPath : 'Accounts/Account',
    Map : function (xml) {
        var pass = xml.Evaluate('Password');
        // if not marked as encrypted (means user has edited the password field) encrypt it
        if (!pass.startsWith('Enc:'))
            this.Password = $Crypto.Encrypt($EncryptionPassword, this.Id, xml.Evaluate('Password'));
        else // otherwise just remove the mark
            this.Password = pass.substr(4);
    }
});

Example

Common use case for importing data

// Assume this is your XML data
// <Root>
//   <Questions>
//     <Question>
//        <Id>145</Id>
//        <Text>What is your favorite product?</Text>
//     </Question>
//     <Question>
//        <Id>146</Id>
//        <Text>Where did you hear about it?</Text>
//     </Question>
//   </Questions>
// </Root>

$Database.ImportFromXml({
    Parameters : {
        TargetSchema : 'Poll',
        TargetTable : 'Questions'
    },
    XPath : 'Questions/Question'
});

// Each "Question" node gets saved into the "Questions" table,
// mapping the inner XML content to the related columns on the table.

Example

Customized Column Update

// Save organization unit positions
$Database.ImportFromXml({
    Parameters : {
        TargetSchema : "HR",
        TargetTable : "OrganizationUnitPositions"
    },
    XPath : "//OrganizationUnitPositions/OrganizationUnitPosition",
    Map : function (xml) {
        // Update position by parent node id
        this.Position = xml.Evaluate('../../Id');
    }
});

By default all matching columns and data model elements are automatically updated by name. If your table columns and data model names are different you can provide a "Map" function to manually map columns to your data model.

Example

Update Only Selected Columns

$Database.ImportFromXml({
    Parameters : {
        TargetSchema : 'Poll',
        TargetTable : 'Questions'
    },
    Columns : [
        {Name : 'Id'},
        {Name : 'Content'},
        {Name : 'Number'},
        {Name : 'Type'}
    ],
    XPath : 'Questions/Question',
    Map : function (node) {
        this.Column('MyId', node.Evaluate('Id'));
        this.Column('MyContent', node.Evaluate('Content'));
        this.Column('MyNumber', node.Evaluate('Number'));
        this.Column('MyType', node.Evaluate('Type'));
    }
});

Example

Nested Insert and Update

$Database.ImportFromXml({                                                                        // Save employee
   Parameters : {
       TargetSchema : 'HR',
       TargetTable : 'Employee'
   },
   XPath : 'Identities/Identity',                                                                // Find rows under Identities/Identity xpath
   ColumnsXPath : 'Employee',                                                                    // Fetch column values from Employee. Final xpath
   Map : function(employeeNode) {
            
       $Database.Get({                                                                           // Fetch matching records from database
           Parameters : {
               TargetSchema : 'HR',
               TargetTable : 'OrganizationUnitPositionMembers'
           },
           Where : {
             Criteria : [
               { Name : 'Employee', Value : employeeNode.Evaluate('Id') },                       // "Employee must equal to Employee/Id xpath value."
               { Name : 'RegistryNumber', Value : '%2', Comparison : 'Like', Condition : 'Or' }  // Another criteria just for sample. "or RegistryNumber must ends with 2"
             ]
           }
       })
       .DeleteAll()                                                                              // Delete existing all rows
       .CreateNew(function() {                                                                   // Create a new row
           this.Employee = employeeNode.Evaluate('Id');                                          // Set Employee column to "Employee/Id" xpath value.
           this.OrganizationUnitPosition = employeeNode.Evaluate('Employee/Position');           // Set OrganizationUnitPosition column to "Employee/Position" xpath value.
       })
       .Save();                                                                                  // Save this table.
  }
});

Saving columns with their related single/multiple entities is handled as below.

Example

Column Update with SubQueries

// Save corporations with the subcorporations
$Database.ImportFromXml({
    Parameters : {
        TargetSchema : 'Document',
        TargetTable : 'Corporations'
    },
    XPath : 'Corporations/Corporation',
    SubQueries : [
        {Name : 'SubCorporations'}
    ]
});

Assume you have the XML below as your form data, two SQL tables Corporations and SubCorporations, and a One-To-Many relation from Corporations table to SubCorporations table which is also named SubCorporations.

Cascade Option Don't forget to set this relation's update rule to "Cascade" to update with sub queries.

Having defined the table columns with identical names to the XML fields, this code lets you save each corporation from XML data into the SQL table, saving also its related SubCorporations into the related SQL table.

Example

Form XML Example

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

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