Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    16

    Unanswered: Nested XML selects

    I am trying to used a stored proc to return an xml string from sysibm.sqltables formatted as follows:

    <TABLES>
    <schema name='schema1'>
    <table>table1</table>
    <table>table2</table>
    </schema>
    <schema name='schema2'>
    <table>table3</table>
    <table>table4></table>
    </schema>
    </TABLES>

    The closest I can get is as follows:

    SELECT XML2CLOB (XMLELEMENT (NAME "TABLES", XMLELEMENT (NAME "schema", XMLATTRIBUTES (table_schem AS "name"),
    XMLAGG (XMLFOREST(table_name AS "table")))))
    FROM sysibm.sqltables GROUP BY table_schem;

    This produces a separate result for each schema because of the GROUP BY statement, but if this is left out an error occurs saying that table_schem needs to be in a GROUP BY statement.

    Does anyone have any ideas?

    Many thanks,
    Ian

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Why would you want to use GROUP BY, you don't use any aggerate function. I think ORDER BY is appropriate.
    Code:
    SELECT XML2CLOB (
      XMLELEMENT (NAME "TABLES", 
       XMLELEMENT (NAME "schema", 
       XMLATTRIBUTES (table_schem AS "name"),
       XMLAGG (XMLFOREST(table_name AS "table"))
       )
      )
    )
    FROM sysibm.sqltables
    ORDER BY table_schem, table_name;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2005
    Posts
    16

    That won't work :o)

    I am using the group by statement, because using the alternative suggested give the error message below:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0119N An expression starting with "TABLE_SCHEM" specified in a SELECT
    clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
    clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
    column function and no GROUP BY clause is specified. LINE NUMBER=5.
    SQLSTATE=42803

    Thanks,
    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •