| How to convert XML content into SQL INSERT statements [message #401790] |
Wed, 06 May 2009 07:38  |
sammydude
Messages: 34
Registered: November 2007
|
Member |
|
|
Hi all,
I'm very new to XML. Forgive me if I don't use technical XML terms.
We are planning to convert SQL queries into XML format using a third party tool.
After that we have to read the XML files and use the tokens to insert into custom tables.
So, basically we have to create INSERT statements using the data stored in the XML file.
How do we go about reading / parsing the XML file in Java?
Pls help!
Regards,
Sam
|
|
|
|
|
|
| Re: How to convert XML content into SQL INSERT statements [message #401794 is a reply to message #401792] |
Wed, 06 May 2009 07:54  |
sammydude
Messages: 34
Registered: November 2007
|
Member |
|
|
This is a specific requirement. The SQL query has to be parsed and the tokens placed under separate buckets (Columns, Tables, Where Clauses). Then the list of columns, tables etc which were parsed earlier will be inserted into designated tables (eg. LIST_COLUMNS, LIST_TABLES etc).
Hence the two pronged process of converting into XML and then using the data in XML to create INSERT statements.
We want to use Java to keep it as an independent tool.
How to do the read the data and load into tables using Oracle?
Regards,
Sam
|
|
|
|
|
|
|
|
| Re: How to convert XML content into SQL INSERT statements [message #401877 is a reply to message #401798] |
Wed, 06 May 2009 15:11 |
sammydude
Messages: 34
Registered: November 2007
|
Member |
|
|
For eg. if the following SQL query is fed into the 3rd party system,
select last_name,job_id,salary from employees a, deptno b
where a.deptno = b.deptno
the output would be,
<?xml version="1.0" ?>
<sqlscript dbvendor="MSSQL">
<fullselectstmt nestlevel="0">
<subselectstmt><selectclause><fieldlist>
<field><fieldname>
<attr>
<sourcetoken toketype="" dbobjtype="field">last_name</sourcetoken>
</attr>
</fieldname>
</field>
<field><fieldname>
<attr>
<sourcetoken toketype="" dbobjtype="field">job_id</sourcetoken>
</attr>
</fieldname>
</field>
<field><fieldname>
<attr>
<sourcetoken toketype="" dbobjtype="field">salary</sourcetoken>
</attr>
</fieldname>
</field>
</fieldlist></selectclause>
<fromclause><joinlist><join nestlevel="0">
<lztable><simpletable><attr>
<sourcetoken toketype="" dbobjtype="table">employees</sourcetoken>
</attr><aliasclause withas="false"><sourcetoken toketype="" dbobjtype="table alias">a</sourcetoken></aliasclause></simpletable></lztable></join><join nestlevel="0">
<lztable><simpletable><attr>
<sourcetoken toketype="" dbobjtype="table">deptno</sourcetoken>
</attr><aliasclause withas="false"><sourcetoken toketype="" dbobjtype="table alias">b</sourcetoken></aliasclause></simpletable></lztable></join></joinlist></fromclause>
<whereclause><expression exprtype="Expr_Comparison" exproop="="><attr>
<sourcetoken toketype="" dbobjtype="table alias">a</sourcetoken>
<sourcetoken toketype="" dbobjtype="unknown">.</sourcetoken>
<sourcetoken toketype="" dbobjtype="field">deptno</sourcetoken>
</attr><attr>
<sourcetoken toketype="" dbobjtype="table alias">b</sourcetoken>
<sourcetoken toketype="" dbobjtype="unknown">.</sourcetoken>
<sourcetoken toketype="" dbobjtype="field">deptno</sourcetoken>
</attr></expression>
</whereclause></subselectstmt></fullselectstmt>
</sqlscript>
So, using the output file, the list of columns (under token "field") last_name,job_id and salary should be inserted into LIST_COLUMNS table. So 3 INSERT statements should be created for 3 columns.
The list of tables (under token "table") employees and dept should be inserted into LIST_TABLES table. So 2 INSERT statements should be created for the 2 tables.
Hope this is clear. 
Regards,
Sam
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
