Convert XML data into Relational Table data using Teiid

In this article I will show how to convert XML data into relational form using Teiid using "XMLTABLE" SQL/XML function. XMLTABLE is defined in SQL/XML 2006 specification and is supported most major databases like Oracle, DB2 etc. XMLTABLE functionality is introduced in Teiid 7.0. If you are already familiar with using this function it is no different in using it in Teiid, however there are couple advantages in using this function in Teiid

  • You can integrate the results with another relational or non-relational source
  • You can stream the XML document contents from files and web services.

 

So, once I started writing this article, did quick google search and came across this very well explained article on XMLTABLE. Just over look any DB2 specific stuff and everything else should be same. I am not going to explain the same stuff here again to avoid duplicity, instead I will show you the same example from this article working in Teiid from various sources.

 

This is a hands on example, so If you do not have the Teiid Server installed, please install Teiid 7.x Server along with JBoss AS 5.1.0. See Teiid documents if you need installation instructions. In Teiid you define Virtual Database (VDB) for doing the data integration. Consult Teiid documents to learn more if you are not familiar VDB concepts. So, lets start with defining a simple Dynamic VDB.

 

employee-vdb.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="employee" version="1">
    <model name="dummy">
        <source name="dummy" translator-name="loopback"/>
    </model>
</vdb>


 

Here I created a VDB "employee" with "dummy" schema (model) inside it. We will develop this as the example progresses.

  1. Deploy this VDB into Teiid. (copy the file into <jboss-as>/server/default/deploy directory)
  2. Start the JBoss AS server. (execute <jboss-as>/bin/run.sh script)
  3. Now use a JDBC SQL Client tool like SquirreL for quick testing and connect the VDB that has been deployed in previous step.

 

Now, lets start with first example. For all our examples this below XML will serve as the document

 

employees.xml

<dept bldg="101">
     <employee id="901">
          <name>
               <first>John</first>
               <last>Doe</last>
          </name>
          <office>344</office>
          <salary currency="USD">55000</salary>
     </employee>
     <employee id="902">
          <name>
               <first>Peter</first>
               <last>Pan</last>
          </name>
          <office>216</office>
          <phone>905-416-5004</phone>
     </employee>
</dept>

 

Example 1: Reading XML from a String (same as reading from a Database Column)

 

If you execute the following SQL,

 

select * from xmltable('/dept/employee' PASSING convert('<dept bldg="101">
    <employee id="901">
        <name>
            <first>John</first>
            <last>Doe</last>
        </name>
        <office>344</office>
        <salary currency="USD">55000</salary>
    </employee>
    <employee id="902">
        <name>
            <first>Peter</first>
            <last>Pan</last>
        </name>
        <office>216</office>
        <phone>905-416-5004</phone>
    </employee>
</dept>', xml) COLUMNS 
   empID     integer     PATH '@id',
   firstname     string     PATH 'name/first',
   lastname     string     PATH 'name/last') AS employee

 

You will see results in relational tabular format as

 

 

empID
firstnamelastname
901JohnDoe
902PeterPan

 

There couple things I modified from the original article where I took this example from

  • Passed in XML as String inline on the SQL query. Then converted it to XML using "CONVERT". If your data is already in SQLXML format you do not need to convert into XML. Alternatively you can also use "XMLPARSE (document xmlstring)" function.
  • Changed data types in "firstname" and "lastname" from "varchar" to "string". This is because Teiid does not support the data type "varchar", the exact equivelent is "string".

 

Example 2: XML data from a file

 

So far the above examples showed you how parse the data from text string, but most often this kind of data resides in a text file. This example will show you how to fetch data from text file and then use XMLTABLE function to convert data to relational form. For this example purpose I am going to use the above XML fragment as file.

 

Before we can access the above file though Teiid, we need re-configure the VDB and define a "source" (aka physical) model, that knows how to access the file from the disk. Now below is modified VDB file.

 

employee-vdb.xml

 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="employee" version="1">
    <model name="department">
        <source name="text-connector" translator-name="file" 
              connection-jndi-name="java:department-file"/>
    </model>
</vdb>



OK, here few things needs explaining. First, I removed the "dummy" model from before, replaced with "department". The "source " element defined what kind of source this model if encapsulating.

  • "name" attribute defines the name of source given by the user. It is used in logging etc.
  • "translator-name" defines the type of source. For this example it is "file". (this can be any database like "oracle", "db2". Check here for complete list.
  • "connection-jndi-name" defines the "jndi" name of the data source in the JBoss AS. For accessing a file, this may seem excessive, however for consistent connection creation and access Teiid used JCA data sources.

 

Now creating a JCA data source for file access posses a interesting problem. No worries, Teiid provides the resource adapters for the file, database, ldap, web service, sales force etc. In JBoss AS, to create a data source you need to create a "xxx-ds.xml" file. Lets create one for the file access for our example.

 

department-ds.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<connection-factories>
   <no-tx-connection-factory>
      <jndi-name>department-file</jndi-name>
      <rar-name>teiid-connector-file.rar</rar-name>
      <connection-definition>javax.resource.cci.ConnectionFactory</connection-definition>                   
      <config-property name="ParentDirectory" 
               type="java.lang.String">{my-directory}</config-property>            
      <max-pool-size>20</max-pool-size>
    </no-tx-connection-factory>
</connection-factories>

 

in the above XML, replace {my-directory} with directory location of where you saved "employees.xml

" file. Then deploy the VDB and "-ds.xml" file in JBoss AS. (copy them both to the deploy directory). Now re-connect to the VDB using SquirreL SQL client again and now you can issue below commands.

 

select employee.* from (call department.getTextFiles('*.xml')) f, 
   xmltable('/dept/employee' PASSING xmlparse(document f.file) COLUMNS 
       empID     integer     PATH '@id',
       firstname     string     PATH 'name/first',
       lastname     string     PATH 'name/last') AS employee

 

You will see the results as

 

empID
firstnamelastname
901JohnDoe
902PeterPan

 

Lets take second and understand what is going on here. This above command takes advantage nested table reference, where table results from one FROM clause Table can be fed into next FROM clause Table in the query. So, in the above query

 

(call department.getTextFiles('*.xml')) f




executes a stored procedure called "getTextFiles" exposed by the file "source" model created above, and its execution results are exposed as a Table aliases to 'f'. The "getTextFiles" procedure exposed by the "file" has the following signature.

 

ResultSet getTextFile (string pathAndPattern)

ResultSet (has following columns)
     - file as clob
     - filePath as string

 

The next segment of the SQL query is exactly same as before with couple minor changes

 

   xmltable('/dept/employee' PASSING xmlparse(document f.file) COLUMNS 
       empID     integer     PATH '@id',
       firstname     string     PATH 'name/first',
       lastname     string     PATH 'name/last') AS employee

 

The contents of the file are read as clob from "f.file" column and passed into XMLPARSE function to convert this CLOB content into a XML content. Rest is exactly same as before.

 

Example 3: XML data from a Web Service

 

In this example we will replace the file data source with a 'web service" data source and will issue similar command. Since we do not have web service lets create one for this example purposes. Following these instructions I created a JAX-WS that will produce the same exact XML contents as before. Here is code snippet. Please see the attached zip for the whole project.

 

@WebService(
           name="Department",
           serviceName="DepartmentService",
           targetNamespace="http://teiid.org"
       )
@SuppressWarnings("nls")
public class DepartmentService {
    
    @WebMethod(operationName="getdepartment", action = "urn:department")
    public Department getDepartment() {
        Department d = new Department();
        d.building = "101";
        
        Employee e1 = new Employee();
        e1.name.firstName = "John";
        e1.name.lastName = "Doe";
        e1.id = "901";
        e1.office = "344";
        e1.salary = new Salary();
        e1.salary.value = "55000";
        d.employees.add(e1);
        
        Employee e2 = new Employee();
        e2.name.firstName = "Perter";
        e2.name.lastName = "Pan";
        e2.id = "902";
        e2.office = "216";
        e2.phone = "905-416-5004";
        
        d.employees.add(e2);        
        
        return d;
    }

    @XmlRootElement(name = "department")
    @XmlAccessorType(XmlAccessType.NONE)
    public static class Department {
        @XmlAttribute(name = "bldg")
        String building;
        
        @XmlElement(name = "employee")
        List<Employee> employees = new ArrayList<Employee>();
    }
    
    @XmlAccessorType(XmlAccessType.NONE)
    public static class Employee {
        
        @XmlAttribute(name = "id")
        String id;
        
        @XmlElement(name = "name")
        Name name = new Name();
        
        @XmlElement(name = "office")
        String office;
        
        @XmlElement(name = "phone")
        String phone;
        @XmlElement(name = "salary")
        Salary salary = null;
        
        @XmlAccessorType(XmlAccessType.NONE)
        static class Name {
            @XmlElement(name = "first")
            String firstName;
            @XmlElement(name = "last")
            String lastName;            
        }
        
        @XmlAccessorType(XmlAccessType.NONE)
        static class Salary {
            @XmlAttribute(name = "currency")
            String currency = "USD";
            @XmlValue
            String value;
        }
    }
}

 

For the "web.xml" look in the attached file. I built the WAR file and then deployed. I tested the web service using the SOAP-UI tool to make sure it is working.

 

Before we can access the above file though Teiid, we need re-configure the VDB and define a "source" (aka physical) model, that knows how to access the web service. Now below is modified VDB file.

 

employee-vdb.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="employee" version="1">
    <model name="department">
        <source name="web-connector" translator-name="ws" connection-jndi-name="java:department-ws"/>
    </model>
</vdb>







 

changes above are same as before, but I switched the "file" source with "web-service" source in the VDB definition. Now we need to define the JCA connector for the web-service model. Here is the xml file for it.

 

department-ws-ds.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<connection-factories>
   <no-tx-connection-factory>
      <jndi-name>department-ws</jndi-name>
      <rar-name>teiid-connector-ws.rar</rar-name>
      <connection-definition>javax.resource.cci.ConnectionFactory</connection-definition>      
      <config-property name="EndPoint">http://localhost:8080/department</config-property>            
      <max-pool-size>20</max-pool-size>
    </no-tx-connection-factory>
</connection-factories>

 

if your endpoint location is different, then replace it in the above XML's "EndPoint" property. Then deploy the -ds.xml and VDB file in JBoss AS. (copy them both to the deploy directory). Now re-connect to the VDB using SquirreL SQL client again and now you can issue below command.

 

select employee.* from (
   call department.invoke(request=>'<teiid:getdepartment xmlns:teiid="http://teiid.org"/>',
                          binding='SOAP11')) f, 
   xmltable(XMLNAMESPACES('http://teiid.org' as "teiid"), 
      '/teiid:getdepartmentResponse/return/employee' PASSING f.result COLUMNS 
       empID     integer    PATH '@id',
       firstname string     PATH 'name/first',
       lastname  string     PATH 'name/last') AS employee

 

this will return results

 

empIDfirstnamelastname
901JohnDoe
902PeterPan

The above query needs some explanation, This above command takes advantage nested table reference, where table results from one FROM clause Table can be fed into next FROM clause Table in the query. So, in the above query

 

   call department.invoke(request=>'<teiid:getdepartment xmlns:teiid="http://teiid.org"/>',
                          binding='SOAP11')) f



 

executes a stored procedure called "invoke" exposed by the web service source model "department" created above, and its execution results are exposed as a Table aliases to 'f'. The "invoke" procedure exposed by the "ws" has the following signature.

 

Procedure:

XML invoke(binding in STRING, action in STRING, request in XML, endpoint in STRING)

Where
  binding = HTTP, SOAP11 or SOAP12
  action  = GET or POST (only applies when binding = 'HTTP'), otherwise defines SOAPAction
  request = valid XML input 
  endpoint= Endpoint for the service
  return XML = result parameter as XML document



Note that all the parameters for the above procedure are not mandatory, see documentation

for defaults. With this procedure you can invoke SOAP calls or HTTP calls. The requirement is your service return a valid XML contents.

 

xmltable(XMLNAMESPACES('http://teiid.org' as "teiid"), 
      '/teiid:getdepartmentResponse/return/employee' PASSING f.result COLUMNS 
       empID     integer    PATH '@id',
       firstname string     PATH 'name/first',
       lastname  string     PATH 'name/last') AS employee

 

This fragment is mostly same as before, the only changes I introduced are with respect to the XML Namespaces. Also, the root element I needed to add extra level resolution.

 

Example 4: Creating View from XML Data

 

For creating a view you can follow the same procedure for creating a view as I described in this article (Text To Table with Teiid), but create a source model for the web-service with the "invoke" procedure, (or text source model, if you are using xml file) and view model with a base table representing the "employee" columns as above resultset with above SQL as its transformation.

 

The real value of Teiid is in its ability to define views over the data from different sources where the end user/developer does not really know where the data is gathered from, all they know is how it is exposed to them. Teiid 7.0 does not support views with the Dynamic VDB, this is on Teiid's roadamp for 8.0 version.

 

Currently to define a view layer, you need to use Teiid Desinger, a eclipse based environment that aids to develop a schema model for use by the Teiid runtime. Teiid Desinger can produces a VDB artifact (this is not a XML file) that can contain views. The below are steps you need to take to create VDB, that has the view model. for your convenience I have attached the eclipse project (XMLView.zip) for this example.

 

  • Download and install Teiid Designer.
  • Start the Designer.
  • Create "Model Project" name it as "XMLView"
  • Create "Metadata Model" with class "Relational" and type "source" and name it as "department". (this is going to web service source)
  • Create a "procedure" called "invoke", and add all the IN paramters (binding, action, request, endpoint). Also add another RETURN parameter and call it "result"
  • Save.
  • Create a another "Metadata Model" with class "Relational" and type "view" and name it as "XMLView".
  • Create a base table in the model, and name it "employee"
  • Add three columns to the "employee" table  
    • empID:int
    • firstname:string
    • lastname:string
  • Now double click on the "employee" table and in the "transformation editor", copy or type in the following code.

 

SELECT 
        employee.* 
FROM 
        (EXEC department.invoke(binding = 'SOAP11', 
              action = null, request = '<teiid:getdepartment xmlns:teiid="http://teiid.org"/>', 
             endpoint = null)) AS f, 
     XMLTABLE(XMLNAMESPACES('http://teiid.org' AS teiid), 
     '/teiid:getdepartmentResponse/return/employee' PASSING result 
              COLUMNS empID integer PATH '@id', 
               firstname string PATH 'name/first', 
               lastname string PATH 'name/last') AS employee 

  • Save
  • Now create a "virtual database" named "employee", and add both the "XMLView" and "department" models into it.
  • On VDB editor, make sure the "translator" name for the "department" is set to "ws" and JNDI name is set to "department-ws". This is similar to "source" element we configured in the Dynamic VDB case.
  • Make sure on the "XMLView" the visible box is checked.
  • Save, and deploy the VDB into the Teiid runtime. Since we previously deployed the "department-ws-ds.xml" file, we already defined the data source for the web-service access.

 

Now using SquirreL tool or eclipse DTP connect to the new VDB "employee" and issue a command like

 

SELECT * FROM employee

 

The results as

 

empID
firstnamelastname
901JohnDoe
902PeterPan

Generating XML data from the Relational Data:

So far we have only seen the capabilities that are describing to consume the XML data in the Teiid. If you need to produce the XML data Teiid provides various SQL/XML functions for that purpose too. Check out this article on the available SQL/XML functions. These are also supported by Teiid. In a future article, I will how show you how to use these SQL/XML functions and create a web service by just using the Teiid Designer.