SQL script parser
Monday, 22 September 2014 · 2 messages
MH
Michael Hancock22 Sep 2014 12:49 #
Hi,
Hope you can help with the following.
I have been asked to create a xquery script that can be used to parse Oracle SQL statements into an XML structure and I would like some pointers on how best to achieve this. Please find below details:
1) The input SQL document is free format text where statements are split by ";" and may be entered over several lines. Example below:
"Create table x
(
Column1 varchar2(10),
Column1 varchar2(10)
);
Comment on column x.column1 "xxxxxxxxxxxxxx";
Alter table x add
("column3" varchar2(10));"
2) The XML output for the above example would be something like:
<sql command="create table" name="x">Create table x (Column1 varchar2(10),Column1 varchar2(10));</sql>
<sql command="comment on" name="column x.column1">Comment on column x.column1 "xxxxxxxxxxxxxx"; </sql>
<sql command="alter table" name="x">Alter table x add ("column3" varchar2(10)); </sql>If you could provide some pointers on how best to achieve this result I would be much appreciated.
Best regards.
Mike
Michael Hancock
Clinical Applications Systems Programmer
Pharm-Olam International
The Brackens, London Road, Ascot
Berkshire, SL5 8BJ, UK
Tel: +44 (0)1344 898 583
Fax: +44 (0)1344 899 199
Email: Michael.Hancock at pharm-olam.com
This e-mail may contain information that is privileged, confidential and/or subject to legal restrictions and penalties regarding it's unauthorized disclosure or use. Unauthorized viewing, copying, distribution, disclosure or other use of this information is prohibited if you are not the intended recipient. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete this e-mail and attachments from your system. Thank you.
CG
Christian Grün22 Sep 2014 14:50 #
Hi Mike,
this is one straightforward solution (there are many others):
let $input := file:read-text('sql.txt')
for $sm in tokenize($input, ";")
let $sm := replace($sm, "^\s+|\s+$", "")
let $sm-tokens := tokenize($sm, "\s")
let $cmd := $sm-tokens[1] || ' ' || $sm-tokens[2]
let $name := $sm-tokens[3]
return <sql command="{ $cmd }" name="{ $name }">{ normalize-space($sm) }</sql>However, please be aware of the obvious restrictions of this code snippet:
* The input parsing is very trivial. As an example, semicolons within quotes will also be treated as delimiters.
* The value of the "name" attribute is the third token of a statement, but this is probably not what you need. Similarly, I assume the command may not always be the first two tokens of the statement.
As such parsing issues will arise in any other programming language as well, the example may be sufficient to give you an idea what needs to be done in general.
Christian
Quoted text · Michael Hancock · 41 lines
On Mon, Sep 22, 2014 at 12:49 PM, Michael Hancock <Michael.Hancock at pharm-olam.com> wrote: > Hi, > > Hope you can help with the following. > > I have been asked to create a xquery script that can be used to parse Oracle SQL statements into an XML structure and I would like some pointers on how best to achieve this. Please find below details: > > 1) The input SQL document is free format text where statements are split by ";" and may be entered over several lines. Example below: > > "Create table x > ( > Column1 varchar2(10), > Column1 varchar2(10) > ); > > Comment on column x.column1 "xxxxxxxxxxxxxx"; > > Alter table x add > ("column3" varchar2(10));" > > 2) The XML output for the above example would be something like: > > <sql command="create table" name="x">Create table x (Column1 varchar2(10),Column1 varchar2(10));</sql> > <sql command="comment on" name="column x.column1">Comment on column x.column1 "xxxxxxxxxxxxxx"; </sql> > <sql command="alter table" name="x">Alter table x add ("column3" varchar2(10)); </sql> > > If you could provide some pointers on how best to achieve this result I would be much appreciated. > > Best regards. > Mike > > Michael Hancock > Clinical Applications Systems Programmer > Pharm-Olam International > The Brackens, London Road, Ascot > Berkshire, SL5 8BJ, UK > Tel: +44 (0)1344 898 583 > Fax: +44 (0)1344 899 199 > Email: Michael.Hancock at pharm-olam.com > This e-mail may contain information that is privileged, confidential and/or subject to legal restrictions and penalties regarding it's unauthorized disclosure or use. Unauthorized viewing, copying, distribution, disclosure or other use of this information is prohibited if you are not the intended recipient. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete this e-mail and attachments from your system. Thank you.
Written for BaseX 7.9. BaseX may have changed since: please check the current documentation.
⚡Generated with XQuery