9.6. Import From XML Data File Source

JBoss Data Virtualization supports XML Files as data sources. You can import from these data sources and create the metamodels required to query your data in minutes. Using the steps below you will define your XML data source, configure your parsing parameters for the XML data file, generate a source model containing the required Teiid procedure and create a view table containing the SQL defining the column data in your XML data file.
As with Teiid Designer's JDBC, Salesforce and WSDL importers, the XML File importer is based on utilizing a specific Data Tools Connection Profile.
The results of the importer will include a source model containing the getTextFiles() procedure or invokeHTTP() procedure which are both supported by JBoss Data Virtualization.
The importer will also create a new view model containing a view table for your selected XML source file. Within the view table will be generated SQL transformation containing the getTextFiles() procedure from your source model as well as the column definitions and parameters required for the Teiid XMLTABLE() function used to query the data file. You can also choose to update an existing view model instead of creating a new view model.
The XMLTABLE function uses XQuery to produce tabular output. The XMLTABLE function is implicitly a nested table and may be correlated to preceding FROM clause entries. XMLTABLE is part of the SQL/XML 2006 specification. 
 XMLTABLE([<NSP>,] xquery-expression [<PASSING>] [COLUMNS <COLUMN>, ... )] AS name 
 COLUMN := name (FOR ORDINALITY | (datatype [DEFAULT expression] [PATH string]))
Teiid Designer will construct the full SQL statement for each view table in the form: 
 SELECT A.entryDate AS entryDate, A.internalAudit AS internalAudit FROM (EXEC CCC.getTextFiles('sample.xml')) AS f, XMLTABLE(XMLNAMESPACES('http://www.kaptest.com/schema/1.0/party' AS pty), '/pty:students/student' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS entryDate FOR ORDINALITY, internalAudit string PATH '/internalAudit') AS A
To import from your XML data file source follow the steps below.
  1. In Model Explorer, right-click and then click Import... or click the File > Import... action in the toolbar or select a project, folder or model in the tree and click Import...
  2. Select the import option Teiid Designer > File Source (XML) >> Source and View Model and click Next>
    Import from XML File Source

    Figure 9.19. Import from XML File Source

  3. The next page of the wizard allows selection of the XML Import mode that specifies whether the XML file is local or remote. The description at the top describes what operations this wizard will perform. Select either the XML file on local file system or XML file via remote URL and click Next>
    XML Import File Options Page

    Figure 9.20. XML Import File Options Page

  4. Select existing or previous connection profile from the drop-down selector or press New... button to launch the New Connection Profile dialog or Edit... to modify/change an existing connection profile prior to selection.
    After selecting a Connection Profile, the XML data file from the connection profile will be displayed in the Data File Name panel. Select the data file you wish to process. The data from this file, along with your custom import options, will be used to construct a view table containing the required SQL transformation for retrieving your data and returning a result set.
    Lastly enter the unique source model name in the Source Model Definition section at the bottom of the page or select an existing source model using the Browse button. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not and whether the model already contains the getTextFiles() procedure. In this case, the source model nor the procedure will be generated.
    When finished with this page, click Next>.
    XML Data File Source Selection Page

    Figure 9.21. XML Data File Source Selection Page

  5. On the next page enter the JNDI name and click Next >.
  6. The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file. This page presents a number of options you can use to customize the Generated SQL Statement, shown in the bottom panel. The to panel contains an XML tree view of your file contents and actions/buttons you can use to create column entries displayed in the middle, Column Information panel.
    To create columns, select a root XML element and right-click select Set as root path action. This populates the root path value. Next, select columns in the tree that you wish to include on your query. You can modify or create custom columns, by using the ADD, DELETE, UP, DOWN to manage the column info in your SQL.
    Note that the Path property value for a column is the selected element's path relative to the defined root path. If no root path is defined all paths are absolute. Each column entry requires a datatype and an optional default value. See the Development Guide Volume 3: Reference Material for details on the XMLTABLE() function.
    When finished with this page, click Next>.
    XML File Delimited Columns Options Page

    Figure 9.22. XML File Delimited Columns Options Page

  7. On the View Model Definition page, select the target folder location where your new view model will be created. You can also select an existing model for your new view tables. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not. Lastly, enter a unique, valid view table name. Click Finish to generate your models and finish the wizard.
    View Model Definition Page

    Figure 9.23. View Model Definition Page