2 Replies Latest reply: Jul 16, 2013 11:05 AM by user8136345 RSS

    Creating XML from Relational Tables using java

    user8136345

      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
          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

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