Import, model and test XML data source over HTTP connection

This article will guide you through an example of connecting to an XML document over HTTP as a relational source, using Teiid Designer.  It is assumed that you have already installed Teiid Designer version 7.6 or higher.  This article is part of a complete demo, but can be used as a stand-alone demo for setting up and testing your XML over HTTP sources.

==================================

Start your local teiid server instance

    NOTE: We will use the Data Preview capability to test our XML source which requires Teiid Designer to be connected to a running server containing Teiid submodule.

In Teiid Designer's Guides view select the Model Remote XML File Source action set

Define Teiid Model Project

• Double-click Define Teiid Model Project action

• Select New... button, enter project name (i.e. MyXmlSource) and click Finish

• Click OK

Create XML URL Connection Profile

• Double-click Create Teiid Remote XML connection action

• On the New Connection Profile page, XML File URL Source should be selected.  Enter PlantCatalogXML for the profile name, then click Next

• Enter the Connection URL:  *http://www.w3schools.com/xml/plant_catalog.xml*

• Click Test Connection button to verify the URL is live then click Finish

Create Source Model Rrom XML Source

• Double-click Create source model from remote XML file source action to launch the Import From XML File Source wizard

• Click Next

  • XML Import mode should be set to XML file via remote URL

• XML Data Files Source Selection page should be complete including selected PlantCatalogXML connection, temporary local XML file, project location set as well as a default source model name defined\

  • Keep source model name ( xml_plant_catalog4709489427151235248_Source) or simplify (i.e. xml_plant_catalog_Source)

• Click Next

• On the XML Data File Import Options page_, expand_ CATALOG schema and expand first PLANT element

• Select PLANT element, right-click select Set as root path action.

• Double-click COMMON, ZONE and PRICE to add as output columns for your result set.

  • The Generated SQL Statement panel contains the transformation of your XML data into relational column data via SELECT * FROM statement.

• Click Next

  • On this page you can define the target view model where the generated view will be created.

  • You can simplify both the View Model name and generated view table name for this source

• • i.e. xml_plant_catalog_View model and plant_info view

• Click Finish

  • You will see two additional Models in the Model Explorer view, PlantCatalogSource and PlantCatalogView.  The source model contains a single invokeHttp function, which retrieves the contents at the specified URL.  The PlantCatalogView model contains a single Plants view table, which exposes the data as a relational table.

• Insure all models are saved.

Test Your Generated Procedure

• Double-click Preview Data action

• In the Preview Data dialog_, click_ browse (...) button and select your new virtual table (i.e. plant_info) in xml_plan_catalog_View model

• Click OK

• Query results will be shown in the SQL Results view

Defined Your VDB

• Double-click Define VDB action to launch the Define VDB dialog

• Click the New... button

• The wizard should already be populated with the PlantCatalogView model.

• Click OK

Test Your VDB

• Double-click Execute VDB action

  • This action

    • Checks that a default server exists and is running

    • Deploys your VBD to the server and creates or locates required data sources

    • Creates a JDBC connection to the deployed VDB via the Eclipse Data Tools framework

    • Opens a SQL Editor window containing a simple query template

    • Enter your desired query statement, select the statement text and right-click select Execute Selected Text

    • Query results will also be displayed in the SQL Results view

Congratulations, you have successfully connected to XML over HTTP!