* How to Join a Database Table & XML Data Conveniently
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:
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL