ImportFromXml¶

Imports XML data into a specified table. For more details, see DataTable.ImportFromXml.

1

$Database.ImportFromXml(query: `Columns` : Array<`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.>
An array of columns to include in the result set.

Map : (node: Xml) => void
A custom mapper function to apply to each node in the result set.

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 global

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

Type : ( ... | ... | ... )
The type of ordering (ascending or descending). If not specified, defaults to "Ascending".>
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<Columns : Array<Expression : ...
An expression for the column, which can be a calculation or transformation.

Name : ...
The name of the column to use in the results. If not specified, the

XPath : ...
An XPath expression to map data to the column.>
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<...>
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 : 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 : Blocks : ...
An array of nested query blocks.

Condition : ...
The condition to combine this block with the next one. If not specified, defaults to "And".

Criteria : ...
An array of criteria to apply within this block.
The criteria to filter the query results.

XPath : string
The XPath expression to export the data to.>
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 : Blocks : Array<...>
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<...>
An array of criteria to apply within this block.
The criteria to filter the query results.

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

Parameters¶

Columns : Array<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.>
An array of columns to include in the result set.

Map : (node: Xml) => void
A custom mapper function to apply to each node in the result set.

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 global

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

Type : ( ... | ... | ... )
The type of ordering (ascending or descending). If not specified, defaults to "Ascending".>
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<Columns : Array<Expression : ...
An expression for the column, which can be a calculation or transformation.

Name : ...
The name of the column to use in the results. If not specified, the

XPath : ...
An XPath expression to map data to the column.>
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<...>
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 : 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 : Blocks : ...
An array of nested query blocks.

Condition : ...
The condition to combine this block with the next one. If not specified, defaults to "And".

Criteria : ...
An array of criteria to apply within this block.
The criteria to filter the query results.

XPath : string
The XPath expression to export the data to.>
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 : Blocks : Array<...>
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<...>
An array of criteria to apply within this block.
The criteria to filter the query results.

XPath : string
The root XPath expression to map the data to. query
    The query object that defines the import parameters.

Returns¶

The DataTable instance representing the imported data.

Remarks¶

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

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.

Map function A map function can be used to transform values before saving, for example, to encrypt a password before saving it to the database. See $Database.ExportToXml for more details.

Example¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

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

Common use case for importing 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

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

Customized Column Update¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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

Update Only Selected Columns¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

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

Nested Insert and Update¶

 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

$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 sample criterion: "or RegistryNumber must end with 2"
             ]
           }
       })
       .DeleteAll()                                                                              // Delete all existing 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 the table.
  }
});

Column Update with SubQueries¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// 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, along with two SQL tables,¶

Corporations and SubCorporations , and a one-to-many relation from the Corporations table to the SubCorporations table, also named SubCorporations .

If the table columns have the same names as the XML fields, this code lets you save each corporation from the XML data into the SQL table while also saving its related¶

SubCorporations into the related SQL table.

Form XML Example¶

 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>

See Also¶

• DataTable
• DataTable.ImportFromXml
• DataTable.Save
• $Database.ExportToXml