Search This Blog

Thursday, September 20, 2018

Generate Nested XML using SQL Statement

Below is a sample query can be used to generate nested XML tag using SQL Statement

SELECT XMLELEMENT(
          "ASSEMBLY"
        , XMLFOREST(msi.segment1 assembly_name
                  , msi.description item_description
                  , ood.organization_code organization_code
                  , bom.assembly_type assembly_type)
        , XMLELEMENT(
             "COMPONENTS_ROWSET"
           , (SELECT XMLAGG(
                        XMLELEMENT(
                           "COMPONENTS"
                         , XMLFOREST(
                              bic.bill_sequence_id bill_sequence_id
                            , msi1.segment1 component_name
                            , msi1.description component_description
                            , bic.item_num item_num
                            , bic.operation_seq_num operation_seq_num
                            , (SELECT XMLAGG(
                                         XMLELEMENT("SUBSTITUTES"
                                                  , XMLFOREST(bsc.component_sequence_id, msi2.segment1 substitute_id))
                                         ORDER BY bsc.component_sequence_id)
                               FROM   bom_substitute_components bsc, mtl_system_items_b msi2
                               WHERE  bsc.component_sequence_id = bic.component_sequence_id
                               AND    bsc.substitute_component_id = msi2.inventory_item_id
                               AND    bom.organization_id = msi2.organization_id) substitute_rowset
                            , (SELECT XMLAGG(
                                         XMLELEMENT(
                                            "REFERENCE"
                                          , XMLFOREST(bsc.component_reference_designator, ref_designator_comment))
                                         ORDER BY bsc.component_sequence_id)
                               FROM   bom_reference_designators bsc
                               WHERE  bsc.component_sequence_id = bic.component_sequence_id) reference_rowset)))
              --                                   )
              FROM   bom_inventory_components bic, mtl_system_items_b msi1
              WHERE  bom.bill_sequence_id = bic.bill_sequence_id
              AND    bic.component_item_id = msi1.inventory_item_id
              AND    bom.organization_id = msi1.organization_id
              AND    bic.item_num < 85))).transform(xmltype('
 
 
  
   
  
 
'))
          c
FROM   bom_bill_of_materials bom, mtl_system_items_b msi, org_organization_definitions ood
WHERE  msi.organization_id = bom.organization_id
AND    msi.inventory_item_id = bom.assembly_item_id
AND    ood.organization_id = msi.organization_id
AND    ood.organization_code = :p_org
AND    msi.segment1 = :p_assembly_item;

Copyright (c) All rights reserved. Presented by Suresh Vaishya