1

we are able to generate the XML structure shown below, 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 :

enter image description here

James Z
  • 12,209
  • 10
  • 24
  • 44
Samir Das
  • 11
  • 1
  • 1
    Adding example data as text, or even better as insert clauses, will make it a lot more simple for anyone to help you. – James Z Jun 07 '20 at 07:32

0 Answers0