Home » Developer & Programmer » JDeveloper, Java & XML » How to convert XML content into SQL INSERT statements
How to convert XML content into SQL INSERT statements [message #401790] Wed, 06 May 2009 07:38 Go to next message
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 #401792 is a reply to message #401790] Wed, 06 May 2009 07:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>How do we go about reading / parsing the XML file in Java?
Is that a requirement to use Java?
Oracle can directly read the XML and load into relational tables.
If you want to use Java, you have to write a parser Or there should be something in Java to do this.

Also, I am lost a little.
Why to want to convert SQL queries into XML and
then convert XML to sql (insert statements) again?
Re: How to convert XML content into SQL INSERT statements [message #401794 is a reply to message #401792] Wed, 06 May 2009 07:54 Go to previous messageGo to next message
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 #401798 is a reply to message #401794] Wed, 06 May 2009 08:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Official Oracle documentation on XML and Oracle's support for XML.

http://www.oracle.com/pls/db102/portal.portal_db?selected=7

There are many nice writeups in web like this
http://www.oracle.com/technology/pub/articles/quinlan-xml.html

Just Google.
>>How to do the read the data and load into tables using Oracle?
Long story short,
You can load the XML as-is into an Oracle and use traditional XML tools like XPATH to query the data.
Or
Convert XML into relational data and load into respective tables in Oracle.
Oracle has a pretty decent support for XML.
Re: How to convert XML content into SQL INSERT statements [message #401826 is a reply to message #401794] Wed, 06 May 2009 10:13 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Do I understand you correctly that you don't just store your data as XML, but the actual SQL-statements as well?
You want to extract SQL-statements from XML (i.e. concatenate select-clause, where-clause, etc)?
Re: How to convert XML content into SQL INSERT statements [message #401877 is a reply to message #401798] Wed, 06 May 2009 15:11 Go to previous message
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. Smile

Regards,
Sam
Previous Topic: Install, Create and work XML DB Oracle 11g
Next Topic: Serially Reusable Java Virtual Machine
Goto Forum:
  


Current Time: Fri Mar 29 05:26:44 CDT 2024