Skip to content

ImportFromXml

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

1
$Database.ImportFromXml(query: QueryWithMappings):DataTable

Parameters

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

Info

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 map columns to your data model manually.

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 .

Warning

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

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