Forum Stats

  • 3,840,090 Users
  • 2,262,565 Discussions
  • 7,901,149 Comments

Discussions

XMLELEMENT grouping a set of data

User_POZ94
User_POZ94 Member Posts: 8 Red Ribbon
edited Jun 11, 2020 2:59PM in SQL & PL/SQL

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><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    <header><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        <trantype>siv</trantype><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        <options>ModelMasterEdit</options><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    </header><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    <detail><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        <record><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Description>test_060420</Description><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Details>Test Details</Details><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Canonical>A1863</Canonical><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Classification>PH</Classification><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Tree>XM</Tree><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Manufacturer>Apple</Manufacturer><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Forecast>Apple Smart Phones</Forecast><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Category>Cellular Phones</Category><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <SerialCaption>Serial Number</SerialCaption><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Description>test_060421</Description><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Details>Test Details</Details><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Canonical>A1863</Canonical><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Classification>PH</Classification><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Tree>XM</Tree><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Manufacturer>Apple</Manufacturer><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Forecast>Apple Smart Phones</Forecast><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Category>Cellular Phones</Category><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <SerialCaption>Serial Number</SerialCaption><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        </record><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    </detail><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span></transaction><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>

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

<transaction><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    <header><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        <trantype>siv</trantype><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        <options>ModelMasterEdit</options><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    </header><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    <detail><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        <record><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Description>test_060420</Description><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Details>Test Details</Details><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Canonical>A1863</Canonical><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Classification>PH</Classification><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Tree>XM</Tree><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Manufacturer>Apple</Manufacturer><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Forecast>Apple Smart Phones</Forecast><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Category>Cellular Phones</Category><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <SerialCaption>Serial Number</SerialCaption><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        </record><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        <record><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Description>test_060421</Description><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Details>Test Details</Details><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Canonical>A1863</Canonical><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Classification>PH</Classification><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Tree>XM</Tree><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Manufacturer>Apple</Manufacturer><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Forecast>Apple Smart Phones</Forecast><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <Category>Cellular Phones</Category><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>            <SerialCaption>Serial Number</SerialCaption><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>        </record><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>    </detail><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span></transaction><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);"></span>

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')),<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>        XMLELEMENT ( "detail",<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>        XMLELEMENT (<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>           "record",<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>           XMLAGG (<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>               XMLELEMENT (evalname(attribute_name), attribute_value)))))<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>              FROM XXCMST_INV_CMT_PRODUCT_TBL i<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>                 WHERE     soa_instance_id = 64202001<code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>                 AND source_system = 'EIS'<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--blue-800);"></span>

Table data :

pastedImage_0.png

PLease help us ASAP.

regards, Sam

Tagged:
User_POZ94

Best Answer

  • Stew Ashton
    Stew Ashton Member Posts: 2,910 Bronze Crown
    edited Jun 7, 2020 9:39AM Answer ✓

    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 igroup by model_number;  -- inner aggregation groups by model_number, outer aggregates everything.

    Best regards,

    Stew Ashton

Answers

  • cormaco
    cormaco Member Posts: 1,961 Silver Crown
    edited Jun 7, 2020 3:25AM

    Please post usable example data:

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

    And don't expect an answer "ASAP":

    Do not mark your question as urgent or ASAP.
  • Stew Ashton
    Stew Ashton Member Posts: 2,910 Bronze Crown
    edited Jun 7, 2020 9:39AM Answer ✓

    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 igroup by model_number;  -- inner aggregation groups by model_number, outer aggregates everything.

    Best regards,

    Stew Ashton

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond
    edited Jun 7, 2020 9:49AM

    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.

    User_POZ94