CustomXML Tree

When developing CustomXML Tree follow these recommendations:

1. CustomXML tree must have only 1 root node.
2. Each node must have Node Name, Parent Node (for all but root node), and Data Source. As Node Name you can choose any string meaningful to you and allowed by the XML standard.
3. Each node may or may not have Condition or Node Attributes.
4. CustomXML Tree nodes are mapped into tags in result CustomXml file in format <node_name> … </node_name>. Node Attributes are mapped into tag attributes in format attribute_name="value". Later a report developer links (maps) attributes to MS Word Content Controls so attribute values are displayed in the final document.
5. It is enough to have only one (root) node to display in the final document values (fields) of 1 record (from a database table or view). In other words, if you need to include in your report content of only 1 database record, you have to create only 1 node.
6. To display several records (in a Word table or list) a developer has to create two nodes (per each Word table): parent and child. Child node contains Attributes whose values will be displayed in Word table columns. The parent node defines a number of selected records. So for each record selected from the parent node data source, a separate nested child xml tag will be generated. All children’s tags will have the same name. For example, the following CustomXML Tree:
   
   may derive this CustomXML document:
   
   See more on tables here.
7. SQL query specified in Data Source field may contain a list of columns (with optional aliases) or there might be just a table or view name. In the latter case, all table/view columns will be selected. Supported datatypes of Data Source columns: VARCHAR2, NVARCHAR2, CHAR, NCHAR, NUMBER, FLOAT, DATE. Columns of all other types will be ignored. Each column name and value (or function from that column value with alias) specified in the Data Source may serve to 1 of 2 purposes (or both):
   • It can be used to create xml node’s tag attribute in the resulting CustomXML (if added into the Node Attributes section).
   • It will be available for descendant nodes for reference using format [ancestor_node_name.datasource_column]. Reference can be made in both Node Details > Condition and Node Attributes > Datasource Column sections.
   Referenced in Data Source objects (e.g. tables) must be prefixed with schema_owner (e.g. hr.employees) if they are not located in Apex application Parsing Schema.
8. If the order of rows in the Word table is important then the Data Source (of the parent node) may contain an ORDER BY clause.
9. The purpose of field Condition is to establish links between a node (more precisely, rows selected by node’s Data Source) and Report Parameters or parent (or ancestor) nodes (more precisely, rows selected by those node’s Data Sources). Field Condition contains filters that restrict the number of selected from Data Source rows. There might be 0, 1, or more filters combined by round brackets and Boolean logic operator (like OR, AND, etc.). Each filter is a string in format datasource_column sql_operator criterion where datasource_column is a column (or column alias) mentioned in Data Source, sql_operator is any valid SQL operator (like =, !=, >, IN, etc.), and criterion is one of three types:
   1. Report Parameter which is referenced in Condition in square bracket notation [REPORT_INPUT.PARAMETER_N] where N is 1 to 5. Condition using this type of filter may look like department_id = to_number([REPORT_INPUT.PARAMETER_1])
   2. Values selected in Data Source of one of the ancestor nodes which are referenced in square brackets [ancestor_node_name.datasource_column_name]. Conditions using this type of filter may look like employee_id = [employees.employee_id]
   3. Values selected in Data Source of the node itself like any other sql-WHERE clauses, e.g. salary > 15000. But it’s preferred to include this type of filter in Data Source itself.
   Technically speaking, strings in square brackets are substituted with either report parameters or values selected from columns of ancestor nodes’ Data Sources. Next, this Condition (with references replaced with real values) is concatenated to Data Source and the resulting sql query is dynamically executed by the Oracle sql engine.
10. Node Attributes section contains a list of Data Source columns (or aliases of functions of those columns) which will be mapped to xml node attributes in generated CustomXML. In Datasource Column you may specify:

    Datasource Column is	Example	Attribute Name
    a reference to a column selected in the node’s Data Source	first_name	optional
    an embraced in square brackets reference to the column selected in node ancestor Data Source	[employees.rownum]	mandatory
    an embraced in square brackets reference to the report parameter	[REPORT_INPUT.PARAMETER_1]	mandatory
    a sql function from any of the above references	UPPER (last_name)	mandatory

    Attribute Name is an alias of Datasource Column and will be used as xml attribute in CustomXML in format attribute_name="value". A format mask is used for the conversion of values of numeric and date types into string datatype. Any valid Oracle format mask may be used.

Below is an example of CustomXML Tree and xml document generated based on that tree. Red arrows depict relationships between data pieces.