How to 'flatten' an XML file?
carlossc May 4, 2010 6:05 AMHello:
How can I 'flatten' an XML file to turn it into a relational table?
I explain:
I have an XML file similar to this:
<?xml version="1.0" encoding="UTF-8" ?>
<shop>
<url1>http://www.....</url1>
<url2>http://www.....</url2>
<category>
<category_id>1</category_id>
<category_name>...</category_name>
<products>
<product>
<product_id>1</product_id>
<product_name>...</product_name>
</product>
<product>
<product_id>2</product_id>
<product_name>...</product_name>
</product>
</products>
</category>
<category>
.....
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)
So:
- 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.
My environment:
S.O: Windows XP SP3.
Teiid Designer 6.2.0