RaqForum 35 No.
472 View  • 
     ...

* How to Join a Database Table & XML Data Conveniently

 
multiple datasources(9) XML(13)

Key words: Database table  XML data  Join

There is no direct way to link them. The usual way is to create a temporary table in the database, parse XML data and write it in (by correlation between it and the database table), and perform the table join. This is inconvenient and inefficient because it adds to workload thanks to parsing and database input and it isn’t real-time. 

The join would be efficient if a standalone computing engine could handle it separately. Actually we have such an engine – esProc. It gets XML data and performs the join directly and on its own.

An example: cities is a MySQL table and state is an XML file; the task is to join them to find the population in each state.

MySQL table cities:

CID  NAME  POPULATION  STATEID

1  New York  8084316.0    2

2  Los Angeles  3798981.0  5

3  Chicago  2886251.0  1

4  Houston  2009834.0  1

5  Philadelphia  1492231.0  2

6  Phoenix  1371960.0  1

state.xml:

<?xml version="1.0"   encoding="utf-8"?>

<data>

<state>

<STATEID>1</STATEID>

<NAME>"Alabama"</NAME>

<ABBR>"AL"</ABBR>

 </state>

 <state>

<STATEID>2</STATEID>

<NAME>"Alaska"</NAME>

<ABBR>"AK"</ABBR>

 </state>

</data>

Final result:

undefined

esProc gets it done with a 5-line script:

A

1

=Mysql.query("select *   from cities where STATEID<=2")

2

=xml(file("/workspace/state.xml").read(),"data/state")

3

=A2.new(STATEID,NAME,ABBR).keys(STATEID)

4

>A1.switch(STATEID,A3:STATEID)

5

=A1.groups(STATEID.NAME:STATE;sum(POPULATION):POPULATION)

esProc’s computing mechanism supports retrieving data from a source in real time to compute. This has two advantages. It doesn’t require database input and thus reduces development workload and enables a slim computing structure. It handles various data sources with one interface and thus maintains consistent computing style.

With the help of esProc SPL, we can conveniently handle many similar computing scenarios. More examples can be found in XML Data Parsing & Calculation.

esProc is integration-friendly. Read How to Call an SPL Script in Java to see how we can easily embedded an SPL script into a Java program.

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.

multiple datasources(9) XML(13)
 
 •  472 View  •