Loading ...
Sorry, an error occurred while loading the content.

968Re: [xml-dbms] Re: All in one answer....

Expand Messages
  • Ronald Bourret
    Apr 12, 2001
      meyappan@... wrote:

      > I am just wondering if we have a flat xml that is with no nested
      > relationship, Is it feasible to do bulk loading of xml data into
      > oracle using direct path load.

      Is "direct path load" a feature of Oracle? If so, XML-DBMS does not use
      it.

      By "flat XML" I assume you mean something like the following:

      <Table>
      <Row>
      <Column1>...</Column1>
      <Column2>...</Column2>
      ...
      </Row>
      <Row>
      ...
      </Row>
      ...
      </Table>

      If this is the case, XML-DBMS is probably overkill, as it is designed
      especially to work with nested XML. If you want to use a
      database-specific bulk-load utility, you can probably write your own
      code to do this fairly easily. Such code would presumably use ODBC
      (which supports bulk loads) or Oracle's own API.

      I've attached a rough example of what a SAX version of this code would
      look like at the end of this message -- you would need to modify it for
      bulk loading. (I have a vague feeling there is a state error somewhere
      in this code, but haven't ever run it so I'm not sure.)

      -- Ron

      The code to transfer data from XML to the database follows a common
      pattern, regardless of whether it uses SAX or DOM:

      1.Table element start: prepare an INSERT statement
      2.Row element start: clear INSERT statement parameters
      3.Column elements: buffer PCDATA and set INSERT statement parameters
      4.Row element end: execute INSERT statement
      5.Table element end: close INSERT statement

      The code does not make any assumptions about the names of the tags. In
      fact, it uses the name of the table-level tag to build the
      INSERT statement and the names of the column-level tags to identify
      parameters in the INSERT statement. Thus, these names
      could correspond exactly to the names in the database or could be mapped
      to names in the database using a configuration file.

      Here is the code using SAX:

      int state = TABLE;
      PreparedStatement stmt;
      StringBuffer data;

      public void startElement(String uri, String name, String qName,
      Attributes attr) {
      if (state == TABLE) {
      stmt = getInsertStmt(name);
      state = ROW;
      } else if (state == ROW) {
      stmt.clearParameters();
      state = COLUMN;
      } else { // if (state == COLUMN)
      data = new StringBuffer();
      }
      }

      public void characters (char[] chars, int start, int length) {
      if (state == COLUMN)
      data.append(chars, start, length);
      }

      public void endElement(String uri, String name, String qName) {
      if (state == TABLE)
      stmt.close();
      else if (state == ROW) {
      stmt.executeUpdate();
      state = TABLE;
      } else { // if (state == COLUMN)
      setParameter(stmt, name, data.toString());
      state = ROW;
      }
      }
    • Show all 9 messages in this topic