This discussion is archived
13 Replies Latest reply: Nov 12, 2012 5:13 PM by TrojanSpirit RSS

Aggregating XML Columns in one single Row in Oracle

TrojanSpirit Newbie
Currently Being Moderated
Hello,

I hv a table named TMTABLE and it has follwoing columns:

ID , NAME , TEXT
1 Orders XML1
2 Orders XML2
3 Orders XML3

where XML1, XML2 and XML3 are follwoing:

XML1:

<?xml version="1.0" encoding="utf-8" ?>
<Rowsets>
     <Rowset>
          <Row>
               <PO Uom="">4500005146</PO>
               <Line Uom="">10</Line>
          </Row>
     </Rowset>
</Rowsets>

XML2:

<?xml version="1.0" encoding="utf-8" ?>
<Rowsets>
     <Rowset>
          <Row>
               <PO Uom="">4500005147</PO>
               <Line Uom="">10</Line>
          </Row>
     </Rowset>
</Rowsets>

XML3:

<?xml version="1.0" encoding="utf-8" ?>
<Rowsets>
     <Rowset>
          <Row>
               <PO Uom="">4500005148</PO>
               <Line Uom="">10</Line>
          </Row>
     </Rowset>
</Rowsets>

Now I want Oracle query to return me this XML1, XML2 and XML3 in only one row and in following format:

<?xml version="1.0" encoding="utf-8" ?>
<Rowset>
     <Row>
          <PO Uom="">4500005146</PO>
          <Line Uom="">10</Line>
     </Row>
     <Row>
          <PO Uom="">4500005147</PO>
          <Line Uom="">10</Line>
     </Row>
     <Row>
          <PO Uom="">4500005148</PO>
          <Line Uom="">10</Line>
     </Row>
</Rowset>

I tried various queries but not able to achieve it. Can anyone please help me how to go about it?
  • 1. Re: Aggregating XML Columns in one single Row in Oracle
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Assuming TEXT column is of XMLType datatype :
    SQL> select xmlelement("Rowset",
      2           xmlagg(x.column_value)
      3         ) as result
      4  from tmtable t
      5     , xmltable('/Rowsets/Rowset/Row' passing t.text) x
      6  ;
     
    RESULT
    --------------------------------------------------------------------------------
    <Rowset><Row>
      <PO Uom="">4500005146</PO>
      <Line Uom="">10</Line>
    </Row>
    <Row>
      <PO Uom="">4500005147</PO>
      <Line Uom="">10</Line>
    </Row>
    <Row>
      <PO Uom="">4500005148</PO>
      <Line Uom="">10</Line>
    </Row>
    </Rowset>
     
  • 2. Re: Aggregating XML Columns in one single Row in Oracle
    TrojanSpirit Newbie
    Currently Being Moderated
    Brilliant odie !

    This is what I wanted but could not achieve it even after trying various methods. Never knew that x.column_value syntax exists !

    Thanks a ton for the help :)
  • 3. Re: Aggregating XML Columns in one single Row in Oracle
    odie_63 Guru
    Currently Being Moderated
    Never knew that x.column_value syntax exists !
    See COLUMN_VALUE Pseudocolumn in the documentation for a detailed explanation of its different usages.
  • 4. Re: Aggregating XML Columns in one single Row in Oracle
    TrojanSpirit Newbie
    Currently Being Moderated
    Hi Odie,

    I have one more query.

    In one of my queries, I intend to use one of the XML nodes in where condition.

    Example:

    select xmlelement("Rowset",
    xmlagg(x.column_value)
    ) as result
    from tmtable t
    , xmltable('/Rowsets/Rowset/Row' passing t.text) x

    where x.PO = 'somevalue'

    but it is not allowing me.

    So I tried to use,

    select xmlelement("Rowset",
    xmlagg(x.column_value)
    ) as result
    from tmtable t
    , xmltable('/Rowsets/Rowset/Row' passing t.text
    COLUMNS
    "PO" PATH 'PO'


    ) x

    where x."PO" = 'somevalue'

    but it now says x."column_value" is not identifier.

    So how can I achieve this?
  • 5. Re: Aggregating XML Columns in one single Row in Oracle
    odie_63 Guru
    Currently Being Moderated
    As explained in the link, COLUMN_VALUE is only valid in the absence of the COLUMNS clause.
    In your case, you have to project the Row in its own XMLType column, along with the PO value :
    SQL> select xmlelement("Rowset",
      2           xmlagg(x.r)
      3         ) as result
      4  from tmtable t
      5     , xmltable(
      6         '/Rowsets/Rowset/Row'
      7         passing t.text
      8         columns po  number  path 'PO'
      9               , r   xmltype path '.'
     10       ) x
     11  where x.po = 4500005147
     12  ;
     
    RESULT
    --------------------------------------------------------------------------------
    <Rowset><Row>
      <PO Uom="">4500005147</PO>
      <Line Uom="">10</Line>
    </Row>
    </Rowset>
     
  • 6. Re: Aggregating XML Columns in one single Row in Oracle
    TrojanSpirit Newbie
    Currently Being Moderated
    In your case, you have to project the Row in its own XMLType column, along with the PO value :
    Yeah, defining r xmltype path '.' is doing what I want !!

    Thanks a ton again !
  • 7. Re: Aggregating XML Columns in one single Row in Oracle
    TrojanSpirit Newbie
    Currently Being Moderated
    Hi Odie,

    I am getting a following error if I need to aggregate many XMLs:

    ORA-19011: Character string buffer too small

    Is there a limit of XMLAGG? How can I achieve unlimited aggregation of XML as my XML size will be dynamic.

    My query is following:


    SELECT

    *CASE WHEN ( '[Param.3]' = 'SELECTED' )*

    THEN (

    Select rtrim(XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) , ' ' ) AS Orders
    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
    COLUMNS "ID" NUMBER(19) PATH 'ID',
    R xmltype path '.'

    *) AS RW*

    *Where ID BETWEEN '[Param.1]' and '[Param.2]'*

    *)*

    *WHEN ('[Param.3]' = 'ALL' )*
    THEN (

    Select rtrim(XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) , ' ' ) AS Orders
    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
    COLUMNS "ID" NUMBER(19) PATH 'ID',
    R xmltype path '.'

    *) AS RW*

    *)*

    END AS Orders

    FROM dual

    Soham

    Edited by: 967327 on Nov 8, 2012 3:46 PM
  • 8. Re: Aggregating XML Columns in one single Row in Oracle
    AlexAnd Guru
    Currently Being Moderated
    >
    Is there a limit of XMLAGG?
    >
    looks like it's limit of string and not of xmlagg


    >
    Select rtrim(XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) , ' ' ) AS Orders
    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
    COLUMNS "ID" NUMBER(19) PATH 'ID',
    R xmltype path '.'
    >
    is string

    and what are you trying to do with xmltype?
    you have xmltype
    >
    Select XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) AS Orders
    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
    COLUMNS "ID" NUMBER(19) PATH 'ID',
    R xmltype path '.'
    >
    and then you want to trim ' ' and get string
    i don't understand Why?


    what's difference between string from xmltype
    >
    CASE WHEN ( '[Param.3]' = 'SELECTED' )

    THEN (

    Select rtrim(XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) , ' ' ) AS Orders
    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
    COLUMNS "ID" NUMBER(19) PATH 'ID',
    R xmltype path '.'
    >
    and
    >

    WHEN ('[Param.3]' = 'ALL' )
    THEN (

    Select rtrim(XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) , ' ' ) AS Orders
    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
    COLUMNS "ID" NUMBER(19) PATH 'ID',
    R xmltype path '.'
    >
    ?
  • 9. Re: Aggregating XML Columns in one single Row in Oracle
    TrojanSpirit Newbie
    Currently Being Moderated
    Hi AlexANd.

    If I use only following (without rtrim) :

    Select XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) AS Orders
    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
    COLUMNS "ID" NUMBER(19) PATH 'ID',
    R xmltype path '.'

    it gives me following error:

    ORA-00932: inconsistent datatypes: expected - got CHAR

    So I dont know what to do :( !
  • 10. Re: Aggregating XML Columns in one single Row in Oracle
    AlexAnd Guru
    Currently Being Moderated
    because you must use one datatype for case
    select case when 1=1 then
      (Select XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) AS Orders
      from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
      COLUMNS "ID" NUMBER(19) PATH 'ID',
      R xmltype path '.') AS RW)
    else '1' end
       from dual
     
    ORA-00932: inconsistent datatypes: expected - got CHAR
     
    SQL> 
     37  select case when 1=1 then
     38    (Select XMLELEMENT("Rowset" , XMLAGG( RW.R ORDER BY RW."ID")) AS Orders
     39    from TMTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT
     40    COLUMNS "ID" NUMBER(19) PATH 'ID',
     41    R xmltype path '.') AS RW)
     42  else xmltype('<x/>') end
     43     from dual
     44  /
     
    CASEWHEN1=1THEN(SELECTXMLELEME
    --------------------------------------------------------------------------------
    <Rowset><Row><PO Uom="">4500005146</PO><Line Uom="">10</Line></Row><Row><PO Uom=
     
    SQL> 
  • 11. Re: Aggregating XML Columns in one single Row in Oracle
    TrojanSpirit Newbie
    Currently Being Moderated
    I am not getting what you are trying to say :(

    Can you explain with my original query that how should I modify it?
  • 12. Re: Aggregating XML Columns in one single Row in Oracle
    AlexAnd Guru
    Currently Being Moderated
    try
    SELECT
     CASE
       WHEN ('[Param.3]' = 'SELECTED')   
        THEN
        (     
         Select XMLELEMENT("Rowset", XMLAGG(RW.R ORDER BY RW.ID)) AS Orders
           from TMTABLE UL,
                 XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT COLUMNS "ID"
                          NUMBER(19) PATH 'ID',
                          R xmltype path '.'                      
                          ) AS RW     
          Where RW.ID BETWEEN '[Param.1]' and '[Param.2]'     
         ) 
       WHEN ('[Param.3]' = 'ALL') THEN
        (     
         Select XMLELEMENT("Rowset", XMLAGG(RW.R ORDER BY RW.ID)) AS Orders
           from TMTABLE UL,
                 XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT COLUMNS "ID"
                          NUMBER(19) PATH 'ID',
                          R xmltype path '.'                      
                          ) AS RW     
         ) 
     END AS Orders
      FROM dual
    -- add
    for me it's awful design

    Edited by: AlexAnd on Nov 8, 2012 11:36 PM
  • 13. Re: Aggregating XML Columns in one single Row in Oracle
    TrojanSpirit Newbie
    Currently Being Moderated
    I tried that but it gave me the same error. :(

    But then I tried another method and it worked:

    SELECT
    CASE WHEN ( '[Param.3]') = 'SELECTED' )
    THEN (

    Select X."Orders".getClobVal()

    FROM

    (
    Select XMLELEMENT("Rowset", XMLAGG(RW.R ORDER BY RW.ID)) AS Orders
    from TMTABLE UL,
    XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT COLUMNS "ID"
    NUMBER(19) PATH 'ID',
    R xmltype path '.'
    ) AS RW
    Where RW.ID BETWEEN '[Param.1]' and '[Param.2]'
    ) X

    ) ......

    Thanks for the help anyways !!

Legend

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