SQL & PL/SQL

Greetings,

I've encountered a massive problem for myself. I'm trying to generate xml data based on the following query structure:

SELECT
    h.id,
    ...
    cast
    (
        multiset
        (
             select
                 m.id,
                 m.header
             from
                 message m
             where
                 m.header = h.id
        )
       as message_tabletype
    )
FROM
    header h
WHERE
    h.direction = 'IN'

so far I've converted this like:

SELECT
    XMLELEMENT
    (
        "HEADER_TABLETYPE",
        XMLELEMENT
        (
            "DIRECTION",
            t1.DIRECTION
        ),
        ....
        XMLELEMENT
        (
            "MESSAGES",
            (
                  select
                      XMLAGG
                      (
                          XMLFOREST
                          (
                              t2.id,
                              t2.header
                          )
                      )
                  from
                      message t2
                  where
                      t2.header = t1.id
            )
        )
    )
FROM
	HEADER t1
WHERE
	t1.DIRECTION='IN'

The problem is that the MESSAGES xml element contains only one item, while it should contain 7 rows.

<!-- What I currently have -->
<HEADER>
    <DIRECTION>IN</DIRECTION>
    ....
    <MESSAGES>
        <ID>64638</ID>
        <HEADER>64639</HEADER>
    </MESSAGES>
</HEADER>

<!-- What I'd need to produce -->
<HEADER>
    <DIRECTION>IN</DIRECTION>
    ....
    <MESSAGES>
        <ID>64638</ID>
        <HEADER>64639</HEADER>
    </MESSAGES>
    <MESSAGES>
        <ID>64640</ID>
        <HEADER>64641</HEADER>
    </MESSAGES>
</HEADER>

I hope I could show you my problem. Tho if I'd have to make it more clear, the resulted query should match the following XSD:

<xsd:schema ...>
    <xsd:element name="HEADER">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="DIRECTION">
                ....
                <xsd:element name="MESSAGES" maxOccurs="unbounded">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="ID" type="xsd:integer" />
                            <xsd:element name="HEADER" type="xsd:integer />
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

@Edit:
I got so far that I'd need something like this:

SELECT
    XMLELEMENT
    (
        "HEADER_TABLETYPE",
        XMLFOREST
        (
            t1.DIRECTION,
            t1.ERRORCODE,
            t1.ACKREFID,
            t1.ERRORREFID,
            t1.DESTINATIONID,
            t1.DESTINATION,
            t1.LAST_MODIFIED,
            t1.ORIGINATORID,
            t1.CREATING_ACTION_ID,
            t1.MESSAGE_TIMESTAMP,
            t1.REFID,
            t1.ERRORTEXT,
            t1.EVENTTYPE,
            t1.COMSID,
            t1.SENT_TIMESTAMP,
            t1.ACKNOWLEDGED,
            t1.ORIGINATOR,
            t1.ID,
            t1.LAST_MODIFIED_BY_REMOTE
        ),
        XMLAGG
        (
            XMLELEMENT
            (
                "MESSAGE_TABLETYPE",
                (
                    SELECT
                        XMLFOREST
                        (
                            t2.id,
                            t2.header
                        )
                    FROM
                        message t2
                    WHERE
                        t2.header = t1.id
                )
            )
        )
    ) AS "RESULT"
FROM
	HEADER t1
WHERE
  t1.direction = 'IN';

However for this I get an Error Message that says:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:
Error at Line: 7 Column: 12

Edited by: Wrath#87 on 2012.09.16. 23:50