This discussion is archived
2 Replies Latest reply: Jul 16, 2013 9:05 AM by user8136345 RSS

Creating XML from Relational Tables using java

user8136345 Newbie
Currently Being Moderated

I would like to create an XML document by querying relational tables in java

 

try {

        connection = getConnection();

        final String qryStr = "select XMLElement( foo, 'bar' ) from dual";

        final OracleXMLQuery qry = new OracleXMLQuery(connection, qryStr);

        final String xmlString = qry.getXMLString();

} ....

 

I would expect this to give the following result that I get from running the statement in SQL Developer

select XMLElement( foo, 'bar' ) from dual

<FOO>bar</FOO>

 

Instead I get

<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: Character ')' is not allowed in an XML tag name.</ERROR>

 

Is this the correct way to go about this?

  • 1. Re: Creating XML from Relational Tables using java
    odie_63 Guru
    Currently Being Moderated
    Is this the correct way to go about this?

    Not really.

    OracleXMLQuery class is the Java-side implementation of DBMS_XMLQUERY APIs.

    It's mostly designed to generate a canonical XML document out of a SQL query.

    Assuming a query like "SELECT col1, col2 FROM my_table", the resulting XML should appear like this :

    <ROWSET>

      <ROW>

        <COL1>123</COL1>

        <COL2>ABC</COL2>

      </ROW>

      <ROW>

        <COL1>456</COL1>

        <COL2>XYZ</COL2>

      </ROW>

      ...

    </ROWSET>

     

    So in your test, since the resulting column is not aliased (XMLElement), you're actually trying to generate something like this :

    <ROWSET>

      <ROW>

        <XMLELEMENT(FOO,'BAR')>

          <FOO>bar</FOO>

        </XMLELEMENT(FOO,'BAR')>

      </ROW>

    <ROWSET>

    which of course is invalid, hence the error message.

     

    If you want to generate only <FOO>bar</FOO> as output, just use a regular PreparedStatement with your query and access the document in the ResultSet with the proper getter.

  • 2. Re: Creating XML from Relational Tables using java
    user8136345 Newbie
    Currently Being Moderated

    Thanks for the answer. I will post a follow up question shortly.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points