13 Replies Latest reply: Nov 12, 2012 7:13 PM by TrojanSpirit RSS

    Aggregating XML Columns in one single Row in Oracle

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