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
On Mon, Sep 22, 2014 at 12:49 PM, Michael Hancock Michael.Hancock@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:
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));"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@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.