3 Replies Latest reply on Jun 7, 2020 1:49 PM by Solomon Yakobson

    XMLELEMENT grouping a set of data

    3834825

      need your help in resolving below issue :

      Below XML Structure we are able to generate where the data is linear with no grouping with respect to the Models.

      <transaction>

          <header>

              <trantype>siv</trantype>

              <options>ModelMasterEdit</options>

          </header>

          <detail>

              <record>

                  <Description>test_060420</Description>

                  <Details>Test Details</Details>

                  <Canonical>A1863</Canonical>

                  <Classification>PH</Classification>

                  <Tree>XM</Tree>

                  <Manufacturer>Apple</Manufacturer>

                  <Forecast>Apple Smart Phones</Forecast>

                  <Category>Cellular Phones</Category>

                  <SerialCaption>Serial Number</SerialCaption>

                  <Description>test_060421</Description>

                  <Details>Test Details</Details>

                  <Canonical>A1863</Canonical>

                  <Classification>PH</Classification>

                  <Tree>XM</Tree>

                  <Manufacturer>Apple</Manufacturer>

                  <Forecast>Apple Smart Phones</Forecast>

                  <Category>Cellular Phones</Category>

                  <SerialCaption>Serial Number</SerialCaption>

              </record>

          </detail>

      </transaction>

      however we need the output in below format by grouping the model attributes with respect to model number

      <transaction>

          <header>

              <trantype>siv</trantype>

              <options>ModelMasterEdit</options>

          </header>

          <detail>

              <record>

                  <Description>test_060420</Description>

                  <Details>Test Details</Details>

                  <Canonical>A1863</Canonical>

                  <Classification>PH</Classification>

                  <Tree>XM</Tree>

                  <Manufacturer>Apple</Manufacturer>

                  <Forecast>Apple Smart Phones</Forecast>

                  <Category>Cellular Phones</Category>

                  <SerialCaption>Serial Number</SerialCaption>

              </record>

              <record>

                  <Description>test_060421</Description>

                  <Details>Test Details</Details>

                  <Canonical>A1863</Canonical>

                  <Classification>PH</Classification>

                  <Tree>XM</Tree>

                  <Manufacturer>Apple</Manufacturer>

                  <Forecast>Apple Smart Phones</Forecast>

                  <Category>Cellular Phones</Category>

                  <SerialCaption>Serial Number</SerialCaption>

              </record>

          </detail>

      </transaction>

      we created below SQL query to generate the 1st XML but unable to generate the desired 2nd xml

      SELECT XMLELEMENT ("transaction",XMLELEMENT("header", XMLELEMENT ("trantype", 'siv'), XMLELEMENT ("options", 'ModelMasterEdit')),

              XMLELEMENT ( "detail",

              XMLELEMENT (

                 "record",

                 XMLAGG (

                     XMLELEMENT (evalname(attribute_name), attribute_value)))))

                    FROM XXCMST_INV_CMT_PRODUCT_TBL i

                       WHERE     soa_instance_id = 64202001

                       AND source_system = 'EIS'

      Table data :

      PLease help us ASAP.

      regards, Sam

        • 1. Re: XMLELEMENT grouping a set of data
          cormaco

          Please post usable example data:

          Re: 2. How do I ask a question on the forums?

           

          And don't expect an answer "ASAP":

          Community Posting Etiquette

          Do not mark your question as urgent or ASAP.

          • 2. Re: XMLELEMENT grouping a set of data
            Stew Ashton

            Oracle allows you to do two levels of aggregation, so GROUP BY MODEL_NUMBER to get the aggregation at the RECORD level, then aggregate the records:

             

            SELECT XMLELEMENT (
              "transaction",
              XMLELEMENT(
                "header", 
                XMLELEMENT ("trantype", 'siv'),
                XMLELEMENT ("options", 'ModelMasterEdit')
              ),
              XMLELEMENT (
                "detail",
                xmlagg(   -- outer aggregation
                  XMLELEMENT (
                    "record",
                    XMLAGG (  -- existing aggregation, now an inner aggregation
                      XMLELEMENT (evalname(attribute_name), attribute_value)
                    )
                  )
                )
              )
            )
            FROM i
            group by model_number;  -- inner aggregation groups by model_number, outer aggregates everything.
            

             

            Best regards,

            Stew Ashton

            • 3. Re: XMLELEMENT grouping a set of data
              Solomon Yakobson

              I'd use SUBSTR+INSTR+REPLACE:

               

              SELECT  XMLSERIALIZE(

                                   CONTENT

                                   XMLTYPE(

                                           SUBSTR(

                                                  T.TRX.GetClobVal(),

                                                  1,

                                                  INSTR(T.TRX.GetClobVal(),'<Description>',1,2) - 1

                                                ) ||

                                           REPLACE(

                                                   SUBSTR(

                                                          T.TRX.GetClobVal(),

                                                          INSTR(T.TRX.GetClobVal(),'<Description>',1,2)

                                                         ),

                                                   '<Description>',

                                                   '</record><record><Description>'

                                                  )

                                          )

                                   INDENT SIZE = 2

                                  ) TRX

                FROM  TBL T

              /

               

              TRX

              --------------------------------------------------------------------------------

              <transaction>

                <header>

                  <trantype>siv</trantype>

                  <options>ModelMasterEdit</options>

                </header>

                <detail>

                  <record>

                    <Description>test_060420</Description>

                    <Details>Test Details</Details>

                    <Canonical>A1863</Canonical>

                    <Classification>PH</Classification>

                    <Tree>XM</Tree>

                    <Manufacturer>Apple</Manufacturer>

                    <Forecast>Apple Smart Phones</Forecast>

                    <Category>Cellular Phones</Category>

                    <SerialCaption>Serial Number</SerialCaption>

                  </record>

                  <record>

                    <Description>test_060421</Description>

                    <Details>Test Details</Details>

                    <Canonical>A1863</Canonical>

                    <Classification>PH</Classification>

                    <Tree>XM</Tree>

                    <Manufacturer>Apple</Manufacturer>

                    <Forecast>Apple Smart Phones</Forecast>

                    <Category>Cellular Phones</Category>

                    <SerialCaption>Serial Number</SerialCaption>

                  </record>

                </detail>

              </transaction>

               

              SQL>

               

              SY.