Create XML with SQL queries (jsp,mySQL)

Discussion in 'Java' started by jonas.donas@gmx.net, Sep 16, 2005.

  1. Guest

    hi ng.

    i want to generate a xml-file which is constructed out of several
    sql-queries from different tables.

    <element1> // insert data from table1
    <element2> // insert data from table2 and so forth...

    this (maybe straightforwarded?) task should be done within a web
    application
    realized with the java server pages, i.e. a user enters data into the
    different tables and then clicks on "create xml" - the result should be
    a result.xml file which will be stored in a temp. directory.

    my question now is, which technologies (tools) are suitable to use
    here?
    sax - dom ? db-access methods are available within java beans.

    your feedback/ideas/URLs are welcome, thanX.

    jay dee

    ps: i know, cocoon is doing this task very good, but i don't want to
    use it ;-)
     
    , Sep 16, 2005
    #1
    1. Advertising

  2. Guest

    Does this help?

    private static final Document transformStatementToDocument(
    Connection con,
    int call_centre_id,
    String sql_statement) throws
    HostedPlatformDAOException {

    PreparedStatement ps = null;
    ResultSet rs = null;

    try {

    DocumentBuilderFactory factory =
    DocumentBuilderFactory.newInstance();
    DocumentBuilder builder =
    factory.newDocumentBuilder();
    Document doc =
    builder.newDocument();
    Element results = doc.createElement("Results");
    doc.appendChild(results);

    initCon(con, call_centre_id);

    ps = con.prepareStatement(sql_statement);
    rs = ps.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();
    int colCount = rsmd.getColumnCount();

    while (rs.next()) {
    Element row = doc.createElement("Row");
    results.appendChild(row);
    for (int ii = 1; ii <= colCount; ii++) {
    String columnName = rsmd.getColumnName(ii);
    Object value = rs.getObject(ii);

    Element node =
    doc.createElement(columnName);

    node.appendChild(doc.createTextNode(value.toString()));
    row.appendChild(node);
    }
    }

    return doc;

    } catch(Exception ex) {
    logger.error("transformStatementToDocument:
    "+ex.getMessage(), ex);
    throw new
    HostedPlatformDAOException(ex.getMessage(), ex);
    } finally {
    if(ps!=null)
    try{ps.close();}catch(Exception ex){}
    if(rs!=null)
    try{rs.close();}catch(Exception ex){}
    }

    }

    iksrazal
    http://www.braziloutsource.com/
     
    , Sep 16, 2005
    #2
    1. Advertising

  3. wrote:
    > hi ng.
    >
    > i want to generate a xml-file which is constructed out of several
    > sql-queries from different tables.
    >
    > <element1> // insert data from table1
    > <element2> // insert data from table2 and so forth...
    >
    > this (maybe straightforwarded?) task should be done within a web
    > application
    > realized with the java server pages, i.e. a user enters data into the
    > different tables and then clicks on "create xml" - the result should be
    > a result.xml file which will be stored in a temp. directory.
    >
    > my question now is, which technologies (tools) are suitable to use
    > here?
    > sax - dom ? db-access methods are available within java beans.
    >
    > your feedback/ideas/URLs are welcome, thanX.
    >
    > jay dee
    >
    > ps: i know, cocoon is doing this task very good, but i don't want to
    > use it ;-)
    >


    Some databases offer the option to select the data in XML format
    directly from the db (e.g. MS SQL Server).

    HTH,
    Ray

    --
    XML is the programmer's duct tape.
     
    Raymond DeCampo, Sep 17, 2005
    #3
  4. Guest

    hi all,
    thanks for your answers, i'll report my hopefully successful tryouts
    asap.

    greets,
    jonas
     
    , Sep 27, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    4,499
  2. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,557
    Crutcher
    Feb 26, 2006
  3. ecoolone
    Replies:
    0
    Views:
    797
    ecoolone
    Jan 3, 2008
  4. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    375
    Aaron [SQL Server MVP]
    Aug 6, 2004
  5. Arpan

    Create SQL Queries!

    Arpan, Sep 14, 2005, in forum: ASP General
    Replies:
    2
    Views:
    185
    Arpan
    Sep 14, 2005
Loading...

Share This Page