Overview
Imports xml data to specified table. Please see DataTable.ImportFromXml for more details.DataTable $Database.ImportFromXml(query: QueryWithMappings)
Arguments
QueryWithMappings query
Query object to import
Remarks
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.
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. } });
Example
Column Update with SubQueries// Save corporations with the subcorporations $Database.ImportFromXml({ Parameters : { TargetSchema : 'Document', TargetTable : 'Corporations' }, XPath : 'Corporations/Corporation', SubQueries : [ {Name : 'SubCorporations'} ] });
Cascade Option Don't forget to set this relation's update rule to "Cascade" to update with sub queries.
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>
Examples
$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); } });
// 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.
// 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'); } });
$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')); } });
$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. } });
// Save corporations with the subcorporations $Database.ImportFromXml({ Parameters : { TargetSchema : 'Document', TargetTable : 'Corporations' }, XPath : 'Corporations/Corporation', SubQueries : [ {Name : 'SubCorporations'} ] });
<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>