|
Exports data to the USoft multi-table format on the basis of a regular expression identifying a set of tables.
Subtype indicators are NOT exported. See also the RestrictiveSupertypeListing parameter in this help topic.
Using this method, you can create an output file with data from multiple tables. You can use XML.IMPORT to re-import this result set into a target repository where each of the tables is present.
Syntax
INVOKE XML.MultiExportTables WITH SELECT
regular-expression
, parameter, value
, ... , ...
|
The required regular-expression identifies the set of tables from which data is exported.
The optional parameter-value pairs specify additional options. See the Parameters section in this help topic.
You can append WHERE clauses to restrict the output to records that match WHERE conditions. See the "Appended WHERE clauses" section in this help topic.
Example 1
This statement, executed in USoft Definer, returns an XML document describing the structure and contents of tables T_TABLE, T_COLUMN and T_DOMAIN:
INVOKE XML.MultiExportTables WITH SELECT
'(T_TABLE|T_COLUMN|T_DOMAIN)'
|
Example 2
This statement, if executed in your User Application, or in any USoft application such as Definer or Authorizer, returns an XML document describing the structure and contents of ALL tables. This may run into errors if the output includes component tables; you can work around this by using usmeta.tables() and selecting only database tables, or at least filtering out the component tables.
INVOKE XML.MultiExportTables WITH SELECT
'(.*)'
|
You can add parameter-value pairs to your statement to specify additional options. This statement exports all data from the TOUR and SCHEDTOUR table, converting whitespaces to entity references, and writing the output to a file named "c:\temp\output.xml":
INVOKE XML.MultiExportTables WITH SELECT
'(TOUR|SCHEDTOUR)'
, 'ConvertWhiteSpaces', 'yes'
, 'WriteOutputTo', 'c:\temp\output.xml'
|
Available parameter settings are in the table below. The order in which you specify settings has no effect. The use of each parameter is optional. If you do not refer to a given parameter, and it has a default value associated with it, the result is as if you specified that default. Both the parameter names and the values are case-insensitive strings.
Parameter
|
Value
|
Description
|
AppendToFile
|
USFile-alias
output-file-name
|
This parameter is used to specify that the output is to be written directly to a file. If the file exists, it will be appended to. To overwrite the existing file, use the WriteOutputTo parameter instead.
It is possible to use a file which has previously been opened using the USFile.Open method. To do this, the alias used in the USFile.Open method must be specified, preceded by 'USFile.'
|
ConvertWhiteSpaces
|
{ yes | no }
|
Value is a string and must be quoted, as in the example earlier in this help topic.
If you specify 'yes' for this parameter, then tab characters, spaces, newline characters and carriage returns/line feeds are converted to 	   
 and 
 respectively. When the resulting XML document is imported using XML Import, the converted values are switched back into tab characters, spaces, newline characters and CR/LFs.
|
EmptyDocOnZeroRows
|
{ yes | no }
|
Value is a string and must be quoted, as in the example earlier in this help topic.
If set to 'no', if the SELECT statement of the XML export does not return any rows, USoft returns an empty string. This makes it difficult to apply any transformation on the result of the XML export.
If set to ‘yes’ (the default), then the output is always an XML document. If no rows are returned, this XML document contains only a root tag with the same name as the info window name.
|
ProcessingInstruction
|
{ yes | no }
|
Value is a string and must be quoted, as in the example earlier in this help topic.
If you specify 'yes' for this parameter, the following top-level processing-instruction is added to the output XML document:
<?usoft-xml version="1.0" action="multi-tables-import" use-io-formats="no"
verify-original-values="no" return-corrected-records ="yes"?>
Otherwise no encoding processing instruction is added to the returned XML document.
|
RestrictiveSupertypeListing
|
{ yes | no }
|
Value is a string and must be quoted, as in the example earlier in this help topic.
If you specify 'yes' for this parameter, only supertype records are output that are not also a subtype (or more than 1 subtype). The number of supertype records listed in the supertype section of the export file may be less than the number of records in the supertype table in the database.
If you specify 'no' for this parameter (the default), these 2 numbers are always equal.
|
WriteOutputTo
|
USFile-alias
output-file-name
|
This parameter is used to specify that the output is to be written directly to a file. If the file exists, it will be overwritten. To prevent this, use the AppendToFile parameter instead.
It is possible to use a file which has previously been opened using the USFile.Open method. To do this, the alias used in the USFile.Open method must be specified, preceded by 'USFile.'
|
XmlDeclaration
|
{ yes | no }
|
Value is a string and must be quoted, as in the example earlier in this help topic.
If you specify 'yes' for this parameter, the default Rules Engine encoding is returned in XML processing instructions. Otherwise no encoding processing instruction is added to the returned XML document.
|
|
You can append WHERE clauses to restrict the output to records that match WHERE conditions. Do this by specifying a named group in your regular expression, and then using the group name as a parameter name to bind the WHERE clause to the table(s) matching the regular expression:
Syntax
INVOKE XML.MultiExportTables WITH SELECT
named-group-regexp, named-group-regexp ...
, group-name, appended-where-clause
, ... , ...
named-group-regexp ::= (?<group-name>)regular-expression
|
Group-name must be an alphanumeric string starting with a letter and not containing whitespace.
Appended-where-clause must be a SQL fragment starting with the WHERE keyword that is valid when appended to an imaginary "SELECT * FROM table" query for each table matching regular-expression. If named-group-regexp is
and appended-where-clause is
WHERE destination = 'AUSTRALIA'
|
then the output is the result of the implied query:
SELECT * FROM TOUR WHERE destination = 'AUSTRALIA'
|
Appended-where-clause may contain AND and OR operators. It may end in an ORDER BY clause. It may not contain the GROUP BY or HAVING keywords. It may not contain subqueries.
Just like XML.Export, XML.MultiExportTables uses only single-table queries. While its result (other than the result of XML.Export) may contain data from multiple tables, data from each table is obtained by a single-table SELECT statement. The impossibility to specify joins is alleviated by the possibility to use substitution variables to extract joined data by substituted key value. For this option, see the "Substitution variables in appended WHERE" section in this help topic. Use XML.SQLExport if you want to export data based on multi-table queries, ie., queries containing joins or subqueries.
Example
This statement writes TOUR and SCHEDTOUR data with destination AUSTRALIA to a file named "c:\temp\tours.xml". The result is an XML document describing Tour and Schedtour information about tours to Australia.
This example illustrates that group-name can be reused for multiple sections of named-group-regexp separated by alternation symbols ( '|' pipe symbols). This works only if the columns used in appended-where-clause exist in each of the referenced tables.
INVOKE XML.MultiExportTables WITH SELECT
'(?<mydestination>TOUR)|(?<mydestination>SCHEDTOUR)'
, 'mydestination', 'where destination = ''AUSTRALIA'''
, 'ConvertWhiteSpaces', 'no'
, 'AppendToFile','c:\temp\tours.xml'
|
The output data of this statement is the result of the implied queries:
SELECT * FROM TOUR WHERE destination = 'AUSTRALIA'
SELECT * FROM SCHEDTOUR WHERE destination = 'AUSTRALIA'
|
|
You can use the $ prefix to reference substitution variables in appended WHERE clauses. You can then specify a value for a substitution variable by adding a parameter-value pair in which parameter is the variable name and value is the substitution value.
This example exports joined data from multiple tables for a person with person ID 84:
INVOKE XML.MultiExportTables WITH SELECT
'(?<mycondition1>PERSON)|(?<mycondition2>SCHEDTOUR)'
, 'mycondition1', 'where person_id = $PID'
, 'mycondition2', 'where booked_by = $PID'
, 'PID', 84
|
The output data of this statement is the result of the implied queries:
SELECT * FROM PERSON WHERE person_id = 84
SELECT * FROM SCHEDTOUR WHERE booked_by = 84
|
Subtype indicators are not exported to the XML document. This is to minimise potential problems with the re-importability of the export document.
As a result, information about which supertypes belong to which subtypes may change on re-import if you choose not to include the subtype records in your export.
If you export data with XML.Export instead of with XML.MultiExportTables, then subtype indicators may or may not be exported to the XML document. A subtype indicator is exported with XML.Export if it is mentioned in the WITH SELECT ... list, and also if that list contains the "*" wildcard.
For details on the re-importability of subtypes, see XML.Import: Known problems and workarounds.
|
See also
Example: exporting only database tables
XML.Export
XML.SQLExport
XML internal component
XML and the Rules Engine
|
