...
DataTable $Database.ImportFromXml(query: QueryWithMappings)
Arguments
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
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> |
...
Types
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);
}
}); |
...
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. |
...
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');
}
}); |
...
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'));
}
}); |
...
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.
}
}); |
...
Code Block | ||
---|---|---|
| ||
// Save corporations with the subcorporations
$Database.ImportFromXml({
Parameters : {
TargetSchema : 'Document',
TargetTable : 'Corporations'
},
XPath : 'Corporations/Corporation',
SubQueries : [
{Name : 'SubCorporations'}
]
}); |
...
language | js |
---|
...
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" ) }