[Metamodel Wiki] Update of "examples/XmlTableMapping" by KasperSorensen

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Metamodel Wiki" for change notification.

The "examples/XmlTableMapping" page has been changed by KasperSorensen:
https://wiki.apache.org/metamodel/examples/XmlTableMapping

Comment:
Added 'XML table mapping' example page

New page:
= XML table mapping =

Since XML files are hierarical and MetaModel tables are tabular, you need to do some mapping. MetaModel provides a mapping model that is XPath based, with a few slight modifications.

Assume we have the following XML document:

{{{
<?xml version="1.0" encoding="UTF-8"?>
<root>
        <organization type="governmental">
                <name>Company A</name>
                <employees>
                        <employee>
                                <name>John Doe</name>
                                <gender>M</gender>
                        </employee>
                        <employee>
                                <name>Jane Doe</name>
                                <gender>F</gender>
                        </employee>
                </employees>
        </organization>

        <organization type="company">
                <name>Company B</name>
                <employees>
                        <employee>
                                <name>Peter</name>
                                <gender>M</gender>
                        </employee>
                        <employee>
                                <name>Bob</name>
                                <gender>M</gender>
                        </employee>
                </employees>
        </organization>
</root>
}}}

Now imagine that you want to have a table of employee names and gender information, and another table with company name and type information. We define our DataContext and those tables like this:

{{{#!java
XmlSaxTableDef employeeTableDef = new XmlSaxTableDef(
        "/root/organization/employees/employee",
        new String[] {
                "/root/organization/employees/employee/name",
                "/root/organization/employees/employee/gender"
        }
);

XmlSaxTableDef organizationTableDef = new XmlSaxTableDef(
        "/root/organization",
        new String[] {
                "/root/organization/name",
                "/root/organization@type"
        }
);

DataContext dc = new XmlSaxDataContext(
        new File("my_file.xml"), employeeTableDef, organizationTableDef);
}}}

As you see, we simply provide some XPath expressions to 1) define the record scope and 2) define paths of individual values (or rather - the column definitions). If you query those tables, you will get datasets like these:

'''Table: /employee'''

||'''row_id'''||'''/name'''||'''/gender'''||
||0||John Doe||M||
||1||Jane Doe||F||
||2||Peter||M||
||3||Bob||M||

'''Table: /organization'''

||'''row_id'''||'''/name'''||'''@type'''||
||0||Company A||governmental||
||1||Company B||company||

This is nice, but you might be thinking: How can I then join these tables? There doesn't seem to be any cross-reference value that we can join or perform lookups by.

To solve this issue, MetaModel provides a modification for XPath, the index(...) function. Say we want to add the organization's id to the employee table (as a foreign key). To archieve that, we will need this modified employee table definition (notice the third value XPath expression):

{{{#!java
XmlSaxTableDef employeeTableDef = new XmlSaxTableDef(
        "/root/organization/employees/employee",
        new String[] {
                "/root/organization/employees/employee/name",
                "/root/organization/employees/employee/gender",
                "index(/root/organization)"
        }
);
}}}

Now if you query the employees table, this will be your result:

||'''row_id'''||'''/name'''||'''/gender'''||'''index(/root/organization)'''||
||0||John Doe||M||0||
||1||Jane Doe||F||0||
||2||Peter||M||1||
||3||Bob||M||1||

Moving on, you will be able to define both joins and lookups using this foreign key. For example:

{{{#!java
Column fk = employeeTable.getColumnByName("index(/root/organization)");
Column empName = employeeTable.getColumnByName("/name");
Column orgId = organizationTable.getColumnByName("row_id");
Column orgName = organizationTable.getColumnByName("/name");

Query q = dc.query().from(employeeTable)
        .innerJoin(organizationTable).on(fk, orgId)
        .select(empName).as("employee")
        .select(orgName).as("company").toQuery();
DataSet ds = dc.executeQuery(q);
}}}

The contents of this queried dataset will now be:

||'''employee'''||'''company'''||
||John Doe||Company A||
||Jane Doe||Company A||
||Peter||Company B||
||Bob||Company B||

----

CategoryExamples