Scriptella: How to fetch data dynamicallly from a clob object and then insert it into database?

I am newbie in using Scriptella.

My requirement is something like this :

1) Firstly, I have a few very large xml files, from which I have to insert data into database. Hence, I decided to insert xml files as clob object. Here is the syntax I used:

<script connection-id="out" >
 insert into DUMMY_TABLE1 values(5, ?{textfile 'D:\Workspace\src\Menu_for_order.xml'})

It has run successfully.

2) Next thing, I want to do - to fetch the Clob data & then parse it as xpath query and finally, extract the text value under xpath field into database column.

For that, I wrote a java janino block inside query block, like this:

 <query connection-id="out">
  select data as "data" from dummy_table1
 <script connection-id="java">

      java.sql.Clob clob = (java.sql.Clob) get("data");

      org.xml.sax.InputSource inputSource = new org.xml.sax.InputSource(clob.getCharacterStream());
      javax.xml.xpath.XPath xpath = javax.xml.xpath.XPathFactory.newInstance().newXPath();
      java.lang.String xpathExpression = "CommunicationCenter/Response/MenuData/Menu/Noun";

      org.w3c.dom.NodeList nodes = (org.w3c.dom.NodeList) xpath.evaluate(xpathExpression, inputSource, javax.xml.xpath.XPathConstants.NODESET);
</script>

My question is- I have to insert the data which are fetched in "nodes" nodelist into the database column.

How can I do that?

for XML, we can write Xpath expression inside the "query" block and then, for each fetched xpath field, we can insert data into DB.

But here, how can I extract the data from Xpath query, provided data is in Clob object? i have to use Java code for getting Clob data. But after that, how can I insert the data since there is another constraint in the Scriptella:

We cannot use script inside a script block. As "get" can be used only inside "script" block, I am bound to use script tag here.

But then, you will tell me to use Global variables. But there is another problem: how many global variable I would declare?? There are many fields inside "nodes" nodelist. So it is not possible to declare global variable for each field, right??

Can anybody give me a hint upon this specific problem?? Can you tell me is it at all possible with Scriptella??

Solution

From the question it's not clear to me what data are you going to insert into the database columns. Is it an XML content, or some particular element or attribute values extracted from the NodeList? I assume that it's the latter. It is correct that scripts cannot be put inside others scripts. You need a query element to feed data to inner elements. Here is a an example how to accomplish that:

<query connection-id="out">
    select data as "data" from dummy_table1
   <query connection-id="java">
       java.sql.Clob clob = (java.sql.Clob) get("data");
       org.xml.sax.InputSource inputSource = new org.xml.sax.InputSource(clob.getCharacterStream());
       javax.xml.xpath.XPath xpath = javax.xml.xpath.XPathFactory.newInstance().newXPath();
      java.lang.String xpathExpression = "CommunicationCenter/Response/MenuData/Menu/Noun";

      org.w3c.dom.NodeList nodes = (org.w3c.dom.NodeList) xpath.evaluate(xpathExpression, inputSource, javax.xml.xpath.XPathConstants.NODESET);
      String someText = nodes.item(0).getTextContent();
      set("someText", someText); //Set variable for the inner script
      query.next(); //Executes inner script and make variables available to it
      <script connection-id="out">
          INSERT INTO TABLE VALUES(?someText);
      </script>
  </query>