How can I 'flatten' an XML file to turn it into a relational table?
I have an XML file similar to this:
<?xml version="1.0" encoding="UTF-8" ?>
and I would like to create a "View model" with a "View" with the columns:
"category_id", "category_name", "product_id" and "product_name".
This view should have a row for each product of the XML, with the id and the name of the category that each product belongs to.
When I Import the "XML Schema as Relational Source Model", Teiid Desiger creates three tables: "shop", "category" and "product".
It adds the attribute "mmid" to each table an creates foreign keys between product and category; and category and shop.
I thought that I could create the "View" that I wanted with a JOIN using the mmid attributes in the JOIN condition. I.e.:
SELECT category_id, category_name, product_id, product_name
FROM product p INNER JOIN category c ON p.category_mmid = c.mmid;
But this query doesn't return anything because the "mmid" values are empty. I thought that mmid had to contain the XPath route to each node when using the XML-Relational File connector (I have the same problem using the XML-Relational HTTP connector)
- Is there is another way of achieving what I want to do, instead of using a JOIN?
- Maybe the JOIN approach is correct, but I have to do something else so the "mmid" columns have values in them?
Thanks in advance.
S.O: Windows XP SP3.
Teiid Designer 6.2.0