Scriptella: XML for DB: type Into from XPATH

Question

I have an XML file that looks like this:

<XML>
  <Table name='test'>
    <Row>
      <Field name='key'>1000</Field>
      <Field name='text'>Test</Field>
    </Row>
  </Table>
</XML>

id to parse this xml and use it in insert statement:

<query  connection-id="in">
    /XML/Table/Row

    <script connection-id="out">
        INSERT INTO X t (
                t.entitykey, 
                t.text
             )
             VALUES 
             (
                 ????????
             );

    </script>
</query>

How do I access a specific field tag from an insert statement in XPATH? We prefer to have one XSD, which takes into account all the layouts of the tables, and not support n xsd for each table, therefore, the design is Field [@name].

Thanks Matthias

Answer 1

The Xpath driver provides a variable nodethat provides a context for executing xpath expressions on the current returned node. You can use the following expression to get the value of a specific field:

<script connection-id="out">
    INSERT INTO X t (t.entitykey, t.text)
         VALUES ( ?{node.getString("./Field[@name = 'text']")} );
</script>