...
Query object to import
This method is a wrapper method forDataTable.ImportFromXmlandDataTable.Savemethod.
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.
...
Code Block |
---|
|
$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
Code Block |
---|
|
// 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
Code Block |
---|
|
// 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 manually map columns to your data model. |
Example
Update Only Selected Columns
Code Block |
---|
|
$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
Code Block |
---|
|
$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.
}
}); |
Example
Column Update with SubQueries
Code Block |
---|
|
// Save corporations with the subcorporations
$Database.ImportFromXml({
Parameters : {
TargetSchema : 'Document',
TargetTable : 'Corporations'
},
XPath : 'Corporations/Corporation',
SubQueries : [
{Name : 'SubCorporations'}
]
}); |
Warning |
---|
Cascade Option Don't forget to set this relation's update rule to "Cascade" to update with sub queries. |
Example
Form XML Example
Code Block |
---|
|
<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> |
...