This discussion is archived
10 Replies Latest reply: Jul 30, 2012 5:46 AM by Kunal RSS

Handling multilevel subqueries in XMLAGG

Kunal Newbie
Currently Being Moderated
Hi All,

After going thru may of the threads and discussions i have written a query to generate XML from oracle database 11g. I am very close to the result but unable to format the inner result of the xml.

The tag <size_b> is repeating for every <size_d> but i want all the <size_d> tags to come under the <size_b> tag. Could anyone help me out please as i have be unsuccessfully trying for a long time.

Thanks.

Here is the query:
-----------------------------------------------------------------------------------------------------------------
SELECT
XMLELEMENT("order",
(
(
SELECT
XMLAGG
(XMLElement("order_h",
XMLAttributes
(
owner as "owner",
order_no as "order_no",
profoma_no as "profoma_po"
),ORDER_D
)
)
FROM
( SELECT owner,
item_parent,
line_no,
order_no,
diff_1,
diff_3,
profoma_no,
XMLAgg
(
XMLElement("order_d",XMLAttributes
(
owner as "owner",
item_parent as "item_no",
line_no as "line_no",
order_no as "order_no",
diff_1 as "color_code",
diff_3 as "manufacturer"

), SIZE_XML)
) ORDER_D
FROM
(
SELECT assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
model_name,
size_range,
pack_code,
profoma_no,
match_id,
match_01,
XMLAgg
(
XMLElement("size",
XMLAttributes
(
assoc_id as "assoc_id",
match_01 as "match_01",
match_id as "match_id",
pack_code as "pack_code",
size_range as "size_range",
model_name as "model_name",
size_id as "size_id"

), SIZE_B)
) SIZE_XML
FROM
(
SELECT assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
model_name,
size_range,
pack_code,
profoma_no,
priority,
total_pct,
pct_ratio,
match_id,
match_01,
row_no,
XMLAgg
(
XMLElement("Size_b",
XMLAttributes
(
assoc_id as "assoc_id",
row_no as "row_no",
pct_ratio as "pct_ratio",
total_pct as "total_pct",
priority as "priority",
ship_pack as "ship_pack"
),SIZE_D
)
) SIZE_B
FROM
(
SELECT assoc_id,
ctos.order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
ctos.item,
model_name,
size_range,
pack_code,
diff_2,
alloc_qty,
plan_pct_ratio,
row_no,
sort_value,
profoma_no,
priority,
total_pct,
pct_ratio,
match_id,
match_01,
XMLAgg
(
XMLElement("size_d",XMLAttributes
(
assoc_id as "assoc_id",
ship_pack as "ship_pack",
ctos.item as "ctn_sku",
diff_2 as "size_code",
alloc_qty as "alloc_qty",
plan_pct_ratio as "plan_pct_ratio",
row_no as "row_no",
sort_value as "sort_value"
)
)
)SIZE_D
FROM
ct_ordsku_extn ctos,
ct_ordhead_extn ctoh,
item_master im
where ctos.order_no=ctoh.order_no
and ctos.item=im.item

GROUP BY
assoc_id,
ctos.order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
ctos.item,
model_name,
size_range,
pack_code,
diff_2,
alloc_qty,
plan_pct_ratio,
row_no,
sort_value,
profoma_no,
priority,
total_pct,
pct_ratio,
match_id,
match_01

)
GROUP BY
assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
model_name,
size_range,
pack_code,
profoma_no,
priority,
total_pct,
pct_ratio,
row_no,
match_id,
match_01
)

GROUP BY
assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
model_name,
size_range,
pack_code,
profoma_no,
match_id,
match_01
)

GROUP BY
owner,
item_parent,
line_no,
order_no,
diff_1,
diff_3,
profoma_no
)
)
)
).extract('/*')

FROM DUAL;
-----------------------------------------------------------------------------------------------------------------------------------------
The result:

<order>
<order_h owner="GD" order_no="400003" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400003" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
</Size_b>
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
<order_h owner="GD" order_no="400004" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400004" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
</Size_b>
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
</order>
-----------------------------------------------------------------------------------------------------------------------------
Expected result:

<order>
<order_h owner="GD" order_no="400003" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400003" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
<order_h owner="GD" order_no="400004" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400004" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
</order>
  • 1. Re: Handling multilevel subqueries in XMLAGG
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Please format your code when posting here, using the
     tag.
    Your query is not readable as it is now.
    
    It's surely possible to get the expected output with your current approach but I'm not gonna write anything without having sample data first.
    
    Anyway, I think you're taking it the wrong way. 
    I guess repeating elements come from different child tables? If so why are you flattening all out in the innermost subquery? That just makes things more complicated.
    
    Post some sample data from the three base tables (DDLs and INSERTs would be great) and I'll show you how to do it.
    
    Thanks.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 2. Re: Handling multilevel subqueries in XMLAGG
    Kunal Newbie
    Currently Being Moderated
    Thanks a lot for responding!
    Reposting with all the details:
    DDLs and inserts
    --------------------------------------------------------------------------------------------------------------------
    CREATE TABLE CT_ORDHEAD_EXTN
    (
      ORDER_NO         NUMBER(8)                    NOT NULL,
      LC_DOC_SUB_DATE  VARCHAR2(20 BYTE),
      FORWARDER        VARCHAR2(20 BYTE),
      HUB              VARCHAR2(20 BYTE),
      BANNER           VARCHAR2(20 BYTE),
      PRD_MANAGER      VARCHAR2(20 BYTE),
      TICKET_TYPE      VARCHAR2(20 BYTE),
      SEASON           NUMBER(3),
      PHASE            NUMBER(3),
      ORDER_TYPE       VARCHAR2(20 BYTE),
      TSS_CALC_COST    NUMBER(20,4),
      TSS_GRS_VALUE    NUMBER(20,4),
      DC_DATE          DATE,
      LAST_SHIP_DATE   DATE,
      PAYMENT_TERMS    VARCHAR2(15 BYTE),
      PAYMENT_TYPE     VARCHAR2(50 BYTE),
      OWNER            VARCHAR2(15 BYTE)
    )
    
    
    
    CREATE TABLE CT_ORDSKU_EXTN
    (
      ASSOC_ID           NUMBER(15),
      LINE_NO            NUMBER(15),
      ORDER_NO           NUMBER(8)                  NOT NULL,
      ITEM               VARCHAR2(25 BYTE),
      SIZE_CODE          VARCHAR2(10 BYTE),
      ALLOC_QTY          NUMBER(12,4),
      SIZE_ID            VARCHAR2(50 BYTE),
      MODEL_NAME         VARCHAR2(50 BYTE),
      SIZE_RANGE         VARCHAR2(10 BYTE),
      PACK_CODE          VARCHAR2(15 BYTE),
      MATCH_ID           VARCHAR2(10 BYTE),
      MATCH_01           VARCHAR2(10 BYTE),
      SHIP_PACK          VARCHAR2(10 BYTE),
      PRIORITY           VARCHAR2(10 BYTE),
      TOTAL_PCT          NUMBER(11,6),
      PCT_RATIO          NUMBER(11,6),
      ROW_NO             NUMBER(5),
      PLAN_PCT_RATIO     NUMBER(11,6),
      SORT_VALUE         NUMBER(5),
      SELLING_CHANNEL    VARCHAR2(20 BYTE),
      LADING_POINT       VARCHAR2(5 BYTE),
      DC_DATE            DATE,
      SHIP_METHOD        VARCHAR2(6 BYTE),
      FOB_POINT          VARCHAR2(5 BYTE),
      DELIVERY_TERMS     VARCHAR2(50 BYTE),
      PROFOMA_NO         NUMBER(8),
      ORDER_DESCRIPTION  VARCHAR2(150 BYTE),
      STORE_TYPE         VARCHAR2(25 BYTE),
      FOLD_CODE          VARCHAR2(20 BYTE),
      UNIT_ELC           NUMBER(20,4),
      QTY_PER_INNER      NUMBER(12,4),
      SUPP_OFFER_NO      NUMBER(20),
      PUBLISH_IND        VARCHAR2(1 BYTE)
    )
    
    
    CREATE TABLE ITEM_MASTER
    (
      ITEM                      VARCHAR2(25 BYTE)   NOT NULL,
      ITEM_NUMBER_TYPE          VARCHAR2(6 BYTE)    NOT NULL,
      FORMAT_ID                 VARCHAR2(1 BYTE),
      PREFIX                    NUMBER(2),
      ITEM_PARENT               VARCHAR2(25 BYTE),
      ITEM_GRANDPARENT          VARCHAR2(25 BYTE),
      PACK_IND                  VARCHAR2(1 BYTE)    NOT NULL,
      ITEM_LEVEL                NUMBER(1)           NOT NULL,
      TRAN_LEVEL                NUMBER(1)           NOT NULL,
      ITEM_AGGREGATE_IND        VARCHAR2(1 BYTE)    NOT NULL,
      DIFF_1                    VARCHAR2(10 BYTE),
      DIFF_1_AGGREGATE_IND      VARCHAR2(1 BYTE)    NOT NULL,
      DIFF_2                    VARCHAR2(10 BYTE),
      DIFF_2_AGGREGATE_IND      VARCHAR2(1 BYTE)    NOT NULL,
      DIFF_3                    VARCHAR2(10 BYTE),
      DIFF_3_AGGREGATE_IND      VARCHAR2(1 BYTE)    NOT NULL,
      DIFF_4                    VARCHAR2(10 BYTE),
      DIFF_4_AGGREGATE_IND      VARCHAR2(1 BYTE)    NOT NULL,
      DEPT                      NUMBER(4)           NOT NULL,
      CLASS                     NUMBER(4)           NOT NULL,
      SUBCLASS                  NUMBER(4)           NOT NULL,
      STATUS                    VARCHAR2(1 BYTE)    NOT NULL,
      ITEM_DESC                 VARCHAR2(250 BYTE)  NOT NULL,
      ITEM_DESC_SECONDARY       VARCHAR2(250 BYTE),
      SHORT_DESC                VARCHAR2(120 BYTE)  NOT NULL,
      DESC_UP                   VARCHAR2(250 BYTE)  NOT NULL,
      PRIMARY_REF_ITEM_IND      VARCHAR2(1 BYTE)    NOT NULL,
      RETAIL_ZONE_GROUP_ID      NUMBER(4),
      COST_ZONE_GROUP_ID        NUMBER(4),
      STANDARD_UOM              VARCHAR2(4 BYTE)    NOT NULL,
      UOM_CONV_FACTOR           NUMBER(20,10),
      PACKAGE_SIZE              NUMBER(12,4),
      PACKAGE_UOM               VARCHAR2(4 BYTE),
      MERCHANDISE_IND           VARCHAR2(1 BYTE)    NOT NULL,
      STORE_ORD_MULT            VARCHAR2(1 BYTE)    NOT NULL,
      FORECAST_IND              VARCHAR2(1 BYTE)    NOT NULL,
      ORIGINAL_RETAIL           NUMBER(20,4),
      MFG_REC_RETAIL            NUMBER(20,4),
      RETAIL_LABEL_TYPE         VARCHAR2(6 BYTE),
      RETAIL_LABEL_VALUE        NUMBER(20,4),
      HANDLING_TEMP             VARCHAR2(6 BYTE),
      HANDLING_SENSITIVITY      VARCHAR2(6 BYTE),
      CATCH_WEIGHT_IND          VARCHAR2(1 BYTE)    NOT NULL,
      WASTE_TYPE                VARCHAR2(6 BYTE),
      WASTE_PCT                 NUMBER(12,4),
      DEFAULT_WASTE_PCT         NUMBER(12,4),
      CONST_DIMEN_IND           VARCHAR2(1 BYTE)    NOT NULL,
      SIMPLE_PACK_IND           VARCHAR2(1 BYTE)    NOT NULL,
      CONTAINS_INNER_IND        VARCHAR2(1 BYTE)    NOT NULL,
      SELLABLE_IND              VARCHAR2(1 BYTE)    NOT NULL,
      ORDERABLE_IND             VARCHAR2(1 BYTE)    NOT NULL,
      PACK_TYPE                 VARCHAR2(1 BYTE),
      ORDER_AS_TYPE             VARCHAR2(1 BYTE),
      COMMENTS                  VARCHAR2(2000 BYTE),
      ITEM_SERVICE_LEVEL        VARCHAR2(6 BYTE),
      GIFT_WRAP_IND             VARCHAR2(1 BYTE)    NOT NULL,
      SHIP_ALONE_IND            VARCHAR2(1 BYTE)    NOT NULL,
      CREATE_DATETIME           DATE                NOT NULL,
      LAST_UPDATE_ID            VARCHAR2(30 BYTE)   NOT NULL,
      LAST_UPDATE_DATETIME      DATE                NOT NULL,
      CHECK_UDA_IND             VARCHAR2(1 BYTE),
      ITEM_XFORM_IND            VARCHAR2(1 BYTE)    NOT NULL,
      INVENTORY_IND             VARCHAR2(1 BYTE)    NOT NULL,
      ORDER_TYPE                VARCHAR2(6 BYTE),
      SALE_TYPE                 VARCHAR2(6 BYTE),
      DEPOSIT_ITEM_TYPE         VARCHAR2(6 BYTE),
      CONTAINER_ITEM            VARCHAR2(25 BYTE),
      DEPOSIT_IN_PRICE_PER_UOM  VARCHAR2(6 BYTE),
      AIP_CASE_TYPE             VARCHAR2(6 BYTE),
      BANDED_ITEM_IND           VARCHAR2(1 BYTE),
      CATCH_WEIGHT_TYPE         VARCHAR2(1 BYTE),
      PERISHABLE_IND            VARCHAR2(1 BYTE)    DEFAULT 'N'                   NOT NULL,
      SOH_INQUIRY_AT_PACK_IND   VARCHAR2(1 BYTE)    DEFAULT 'N'                   NOT NULL,
      NOTIONAL_PACK_IND         VARCHAR2(1 BYTE)    DEFAULT 'N'                   NOT NULL,
      CATCH_WEIGHT_UOM          VARCHAR2(4 BYTE)
    )
    
    
    INSERT Statements
    -------------------------------------------------------------------------------------------------------------------------------------
    SET DEFINE OFF;
    Insert into CUSTOM.ITEM_MASTER
       (ITEM, ITEM_NUMBER_TYPE, FORMAT_ID, PREFIX, ITEM_PARENT, 
        ITEM_GRANDPARENT, PACK_IND, ITEM_LEVEL, TRAN_LEVEL, ITEM_AGGREGATE_IND, 
        DIFF_1, DIFF_1_AGGREGATE_IND, DIFF_2, DIFF_2_AGGREGATE_IND, DIFF_3, 
        DIFF_3_AGGREGATE_IND, DIFF_4, DIFF_4_AGGREGATE_IND, DEPT, CLASS, 
        SUBCLASS, STATUS, ITEM_DESC, ITEM_DESC_SECONDARY, SHORT_DESC, 
        DESC_UP, PRIMARY_REF_ITEM_IND, RETAIL_ZONE_GROUP_ID, COST_ZONE_GROUP_ID, STANDARD_UOM, 
        UOM_CONV_FACTOR, PACKAGE_SIZE, PACKAGE_UOM, MERCHANDISE_IND, STORE_ORD_MULT, 
        FORECAST_IND, ORIGINAL_RETAIL, MFG_REC_RETAIL, RETAIL_LABEL_TYPE, RETAIL_LABEL_VALUE, 
        HANDLING_TEMP, HANDLING_SENSITIVITY, CATCH_WEIGHT_IND, WASTE_TYPE, WASTE_PCT, 
        DEFAULT_WASTE_PCT, CONST_DIMEN_IND, SIMPLE_PACK_IND, CONTAINS_INNER_IND, SELLABLE_IND, 
        ORDERABLE_IND, PACK_TYPE, ORDER_AS_TYPE, COMMENTS, ITEM_SERVICE_LEVEL, 
        GIFT_WRAP_IND, SHIP_ALONE_IND, CREATE_DATETIME, LAST_UPDATE_ID, LAST_UPDATE_DATETIME, 
        CHECK_UDA_IND, ITEM_XFORM_IND, INVENTORY_IND, ORDER_TYPE, SALE_TYPE, 
        DEPOSIT_ITEM_TYPE, CONTAINER_ITEM, DEPOSIT_IN_PRICE_PER_UOM, AIP_CASE_TYPE, BANDED_ITEM_IND, 
        CATCH_WEIGHT_TYPE, PERISHABLE_IND, SOH_INQUIRY_AT_PACK_IND, NOTIONAL_PACK_IND, CATCH_WEIGHT_UOM)
     Values
       ('102526821', 'ITEM', NULL, NULL, '6534580', 
        NULL, 'N', 2, 2, 'N', 
        '001', 'N', 'M', 'N', '102218', 
        'N', 'CA', 'N', 504, 43, 
        1, 'A', 'NEW PANT', 'NOUVEAU PANTALON', 'NEW PANT', 
        'NEW PANT', 'N', 1, 1000, 'EA', 
        NULL, NULL, NULL, 'Y', 'E', 
        'N', NULL, NULL, NULL, NULL, 
        NULL, NULL, 'N', NULL, NULL, 
        NULL, 'N', 'N', 'N', 'Y', 
        'Y', NULL, NULL, NULL, NULL, 
        'N', 'N', TO_DATE('05/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'KGUPTA_RMS', TO_DATE('07/19/2012 10:35:05', 'MM/DD/YYYY HH24:MI:SS'), 
        'N', 'N', 'Y', NULL, NULL, 
        NULL, NULL, NULL, NULL, NULL, 
        NULL, 'N', 'N', 'N', NULL);
    Insert into CUSTOM.ITEM_MASTER
       (ITEM, ITEM_NUMBER_TYPE, FORMAT_ID, PREFIX, ITEM_PARENT, 
        ITEM_GRANDPARENT, PACK_IND, ITEM_LEVEL, TRAN_LEVEL, ITEM_AGGREGATE_IND, 
        DIFF_1, DIFF_1_AGGREGATE_IND, DIFF_2, DIFF_2_AGGREGATE_IND, DIFF_3, 
        DIFF_3_AGGREGATE_IND, DIFF_4, DIFF_4_AGGREGATE_IND, DEPT, CLASS, 
        SUBCLASS, STATUS, ITEM_DESC, ITEM_DESC_SECONDARY, SHORT_DESC, 
        DESC_UP, PRIMARY_REF_ITEM_IND, RETAIL_ZONE_GROUP_ID, COST_ZONE_GROUP_ID, STANDARD_UOM, 
        UOM_CONV_FACTOR, PACKAGE_SIZE, PACKAGE_UOM, MERCHANDISE_IND, STORE_ORD_MULT, 
        FORECAST_IND, ORIGINAL_RETAIL, MFG_REC_RETAIL, RETAIL_LABEL_TYPE, RETAIL_LABEL_VALUE, 
        HANDLING_TEMP, HANDLING_SENSITIVITY, CATCH_WEIGHT_IND, WASTE_TYPE, WASTE_PCT, 
        DEFAULT_WASTE_PCT, CONST_DIMEN_IND, SIMPLE_PACK_IND, CONTAINS_INNER_IND, SELLABLE_IND, 
        ORDERABLE_IND, PACK_TYPE, ORDER_AS_TYPE, COMMENTS, ITEM_SERVICE_LEVEL, 
        GIFT_WRAP_IND, SHIP_ALONE_IND, CREATE_DATETIME, LAST_UPDATE_ID, LAST_UPDATE_DATETIME, 
        CHECK_UDA_IND, ITEM_XFORM_IND, INVENTORY_IND, ORDER_TYPE, SALE_TYPE, 
        DEPOSIT_ITEM_TYPE, CONTAINER_ITEM, DEPOSIT_IN_PRICE_PER_UOM, AIP_CASE_TYPE, BANDED_ITEM_IND, 
        CATCH_WEIGHT_TYPE, PERISHABLE_IND, SOH_INQUIRY_AT_PACK_IND, NOTIONAL_PACK_IND, CATCH_WEIGHT_UOM)
     Values
       ('102526855', 'ITEM', NULL, NULL, '6534580', 
        NULL, 'N', 2, 2, 'N', 
        '001', 'N', 'S', 'N', '102218', 
        'N', 'CA', 'N', 504, 43, 
        1, 'A', 'NEW PANT', 'NOUVEAU PANTALON', 'NEW PANT', 
        'NEW PANT', 'N', 1, 1000, 'EA', 
        NULL, NULL, NULL, 'Y', 'E', 
        'N', NULL, NULL, NULL, NULL, 
        NULL, NULL, 'N', NULL, NULL, 
        NULL, 'N', 'N', 'N', 'Y', 
        'Y', NULL, NULL, NULL, NULL, 
        'N', 'N', TO_DATE('05/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'KGUPTA_RMS', TO_DATE('07/19/2012 10:36:42', 'MM/DD/YYYY HH24:MI:SS'), 
        'N', 'N', 'Y', NULL, NULL, 
        NULL, NULL, NULL, NULL, NULL, 
        NULL, 'N', 'N', 'N', NULL);
    COMMIT;
    
    ---------------------------------------------------------------------------------------------------------
    SET DEFINE OFF;
    Insert into CT_ORDHEAD_EXTN
       (ORDER_NO, LC_DOC_SUB_DATE, FORWARDER, HUB, BANNER, 
        PRD_MANAGER, TICKET_TYPE, SEASON, PHASE, ORDER_TYPE, 
        TSS_CALC_COST, TSS_GRS_VALUE, DC_DATE, LAST_SHIP_DATE, PAYMENT_TERMS, 
        PAYMENT_TYPE, OWNER)
     Values
       (400014, '2011-10-26', 'WELL', 'KNITS', 'G', 
        NULL, 'GPT', 101, 1, 'IMPR', 
        40948.68, 34455.2, TO_DATE('11/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '64', 
        'DP', 'GD');
    Insert into CUSTOM.CT_ORDHEAD_EXTN
       (ORDER_NO, LC_DOC_SUB_DATE, FORWARDER, HUB, BANNER, 
        PRD_MANAGER, TICKET_TYPE, SEASON, PHASE, ORDER_TYPE, 
        TSS_CALC_COST, TSS_GRS_VALUE, DC_DATE, LAST_SHIP_DATE, PAYMENT_TERMS, 
        PAYMENT_TYPE, OWNER)
     Values
       (400020, '2011-10-26', 'WELL', 'KNITS', 'G', 
        NULL, 'GPT', 101, 1, 'IMPR', 
        40948.68, 34455.2, TO_DATE('11/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '64', 
        'DP', 'GD');
    COMMIT;
    ---------------------------------------------------------------------------------------------------------
    SET DEFINE OFF;
    Insert into CT_ORDSKU_EXTN
       (ASSOC_ID, LINE_NO, ORDER_NO, ITEM, SIZE_CODE, 
        ALLOC_QTY, SIZE_ID, MODEL_NAME, SIZE_RANGE, PACK_CODE, 
        MATCH_ID, MATCH_01, SHIP_PACK, PRIORITY, TOTAL_PCT, 
        PCT_RATIO, ROW_NO, PLAN_PCT_RATIO, SORT_VALUE, SELLING_CHANNEL, 
        LADING_POINT, DC_DATE, SHIP_METHOD, FOB_POINT, DELIVERY_TERMS, 
        PROFOMA_NO, ORDER_DESCRIPTION, STORE_TYPE, FOLD_CODE, UNIT_ELC, 
        QTY_PER_INNER, SUPP_OFFER_NO, PUBLISH_IND)
     Values
       (2404, 1, 400014, '102526821', 'M', 
        200, 'SIZE SCALE 2', 'SIZE SCALE 2', '2', 'BULK', 
        '14', NULL, 'BULK', 'BULK', 100, 
        NULL, 0, 28.2, NULL, 'GRG', 
        NULL, TO_DATE('11/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OCN', '57035', 'FOB', 
        409536, 'LUREX STRIPED CAMI', 'FLOW', 'BASIC', 3.09, 
        1, 22713, 'N');
    Insert into CT_ORDSKU_EXTN
       (ASSOC_ID, LINE_NO, ORDER_NO, ITEM, SIZE_CODE, 
        ALLOC_QTY, SIZE_ID, MODEL_NAME, SIZE_RANGE, PACK_CODE, 
        MATCH_ID, MATCH_01, SHIP_PACK, PRIORITY, TOTAL_PCT, 
        PCT_RATIO, ROW_NO, PLAN_PCT_RATIO, SORT_VALUE, SELLING_CHANNEL, 
        LADING_POINT, DC_DATE, SHIP_METHOD, FOB_POINT, DELIVERY_TERMS, 
        PROFOMA_NO, ORDER_DESCRIPTION, STORE_TYPE, FOLD_CODE, UNIT_ELC, 
        QTY_PER_INNER, SUPP_OFFER_NO, PUBLISH_IND)
     Values
       (2404, 1, 400014, '102526855', 'S', 
        200, 'SIZE SCALE 2', 'SIZE SCALE 2', '2', 'BULK', 
        '14', NULL, 'BULK', 'BULK', 100, 
        NULL, 0, 32.4, NULL, 'GRG', 
        NULL, TO_DATE('11/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OCN', '57035', 'FOB', 
        409536, 'LUREX STRIPED CAMI', 'FLOW', 'BASIC', 3.09, 
        1, 22713, 'N');
    Insert into CUSTOM.CT_ORDSKU_EXTN
       (ASSOC_ID, LINE_NO, ORDER_NO, ITEM, SIZE_CODE, 
        ALLOC_QTY, SIZE_ID, MODEL_NAME, SIZE_RANGE, PACK_CODE, 
        MATCH_ID, MATCH_01, SHIP_PACK, PRIORITY, TOTAL_PCT, 
        PCT_RATIO, ROW_NO, PLAN_PCT_RATIO, SORT_VALUE, SELLING_CHANNEL, 
        LADING_POINT, DC_DATE, SHIP_METHOD, FOB_POINT, DELIVERY_TERMS, 
        PROFOMA_NO, ORDER_DESCRIPTION, STORE_TYPE, FOLD_CODE, UNIT_ELC, 
        QTY_PER_INNER, SUPP_OFFER_NO, PUBLISH_IND)
     Values
       (2404, 1, 400020, '102526855', 'S', 
        200, 'SIZE SCALE 2', 'SIZE SCALE 2', '2', 'BULK', 
        '14', NULL, 'BULK', 'BULK', 100, 
        NULL, 0, 32.4, NULL, 'GRG', 
        NULL, TO_DATE('11/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OCN', '57035', 'FOB', 
        409536, 'LUREX STRIPED CAMI', 'FLOW', 'BASIC', 3.09, 
        1, 22713, 'N');
    Insert into CUSTOM.CT_ORDSKU_EXTN
       (ASSOC_ID, LINE_NO, ORDER_NO, ITEM, SIZE_CODE, 
        ALLOC_QTY, SIZE_ID, MODEL_NAME, SIZE_RANGE, PACK_CODE, 
        MATCH_ID, MATCH_01, SHIP_PACK, PRIORITY, TOTAL_PCT, 
        PCT_RATIO, ROW_NO, PLAN_PCT_RATIO, SORT_VALUE, SELLING_CHANNEL, 
        LADING_POINT, DC_DATE, SHIP_METHOD, FOB_POINT, DELIVERY_TERMS, 
        PROFOMA_NO, ORDER_DESCRIPTION, STORE_TYPE, FOLD_CODE, UNIT_ELC, 
        QTY_PER_INNER, SUPP_OFFER_NO, PUBLISH_IND)
     Values
       (2404, 1, 400020, '102526821', 'M', 
        200, 'SIZE SCALE 2', 'SIZE SCALE 2', '2', 'BULK', 
        '14', NULL, 'BULK', 'BULK', 100, 
        NULL, 0, 28.2, NULL, 'GRG', 
        NULL, TO_DATE('11/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OCN', '57035', 'FOB', 
        409536, 'LUREX STRIPED CAMI', 'FLOW', 'BASIC', 3.09, 
        1, 22713, 'N');
    COMMIT;
    ------------------------------------------------------------------------------------------------------------
    SQL Query to generate the XML
    -----------------------------------------------
    SELECT 
       XMLELEMENT("order",
          (
             (
                SELECT
                  XMLAGG
                   (XMLElement("order_h",
                     XMLAttributes
                        (
                         owner as "owner",
                         order_no as "order_no", 
                         profoma_no as "profoma_po"
                        ),ORDER_D
                    )
                   )
                   FROM 
                    ( SELECT owner,    
                             item_parent,
                             line_no,  
                             order_no, 
                             diff_1,     
                             diff_3,
                             profoma_no,
                             XMLAgg
                              (
                               XMLElement("order_d",XMLAttributes
                                             (
                                               owner as "owner",
                                               item_parent as "item_no",
                                               line_no as "line_no",
                                               order_no as "order_no",
                                               diff_1 as "color_code",
                                               diff_3 as "manufacturer"
                                               
                                             ), SIZE_XML)
                              ) ORDER_D
                          FROM
                            (
                              SELECT assoc_id,
                                     order_no,
                                     owner,
                                     item_parent,
                                     line_no,
                                     diff_1,
                                     diff_3,
                                     size_id,
                                     model_name,
                                     size_range,
                                     pack_code,
                                     profoma_no,
                                     match_id,
                                     match_01,
                                     XMLAgg
                                      (
                                        XMLElement("size",
                                            XMLAttributes
                                             ( 
                                               assoc_id as "assoc_id",
                                               match_01 as "match_01",
                                               match_id as "match_id",
                                               pack_code as "pack_code",
                                               size_range as "size_range",
                                               model_name as "model_name",
                                               size_id as "size_id"
                                               
                                             ), SIZE_B)
                                       ) SIZE_XML
                                   FROM
                                     (
                                        SELECT assoc_id,
                                               order_no,
                                               owner,
                                               item_parent,
                                               line_no,
                                               diff_1,
                                               diff_3,
                                               size_id, 
                                               ship_pack,
                                               model_name,
                                               size_range,
                                               pack_code,
                                               profoma_no,
                                               priority, 
                                               total_pct,
                                               pct_ratio,
                                               match_id,
                                               match_01,
                                               row_no,
                                               XMLAgg
                                                 (
                                                  XMLElement("Size_b",
                                                        XMLAttributes
                                                            (
                                                            assoc_id  as "assoc_id",
                                                            row_no    as "row_no",
                                                            pct_ratio as "pct_ratio",
                                                            total_pct as "total_pct",
                                                            priority  as "priority",
                                                            ship_pack as "ship_pack"
                                                            ),SIZE_D
                                                   )
                                                 ) SIZE_B
                                                FROM
                                                 (
                                                   SELECT assoc_id,
                                                          ctos.order_no,
                                                          owner,
                                                          item_parent,
                                                          line_no,
                                                          diff_1,
                                                          diff_3,
                                                          size_id,
                                                          ship_pack,
                                                          ctos.item,
                                                          model_name,
                                                          size_range,
                                                          pack_code,
                                                          diff_2,
                                                          alloc_qty,
                                                          plan_pct_ratio,
                                                          row_no,
                                                          sort_value,
                                                          profoma_no,
                                                          priority,
                                                          total_pct,
                                                          pct_ratio,
                                                          match_id,
                                                          match_01,
                                                          XMLAgg
                                                           (
                                                             XMLElement("size_d",XMLAttributes
                                                               (
                                                               assoc_id       as "assoc_id",
                                                               ship_pack      as "ship_pack",
                                                               ctos.item      as "ctn_sku",
                                                               diff_2         as "size_code",
                                                               alloc_qty      as "alloc_qty",
                                                               plan_pct_ratio as "plan_pct_ratio",
                                                               row_no         as "row_no",
                                                               sort_value     as "sort_value"
                                                               )
                                                              ) 
                                                           )SIZE_D
                                                        FROM 
                                                            ct_ordsku_extn ctos,
                                                            ct_ordhead_extn ctoh,
                                                            item_master im
                                                        where ctos.order_no=ctoh.order_no
                                                        and   ctos.item=im.item    
                                                            
                                                        GROUP BY
                                                            assoc_id,
                                                            ctos.order_no,
                                                            owner,
                                                            item_parent,
                                                            line_no,
                                                            diff_1,
                                                            diff_3,
                                                            size_id,
                                                            ship_pack,
                                                            ctos.item,
                                                            model_name,
                                                            size_range,
                                                            pack_code,
                                                            diff_2,
                                                            alloc_qty,
                                                            plan_pct_ratio,
                                                            row_no, 
                                                            sort_value,   
                                                            profoma_no,
                                                            priority,
                                                            total_pct,
                                                            pct_ratio,
                                                            match_id,
                                                            match_01
                                                            
                                                         )
                                                       GROUP BY
                                                         assoc_id,
                                                         order_no,
                                                         owner,
                                                         item_parent,
                                                         line_no,
                                                         diff_1,
                                                         diff_3,
                                                         size_id, 
                                                         ship_pack,
                                                         model_name,
                                                         size_range,
                                                         pack_code,
                                                         profoma_no,
                                                         priority, 
                                                         total_pct,
                                                         pct_ratio,
                                                         row_no,
                                                         match_id,
                                                         match_01
                                                       )
                                                       
                                                   GROUP BY
                                                      assoc_id,
                                                      order_no,
                                                      owner,
                                                      item_parent,
                                                      line_no,
                                                      diff_1,
                                                      diff_3,
                                                      size_id,
                                                      model_name,
                                                      size_range,
                                                      pack_code,
                                                      profoma_no,
                                                      match_id,
                                                      match_01
                                                 )
                                                
                                               GROUP BY
                                                   owner,    
                                                   item_parent,
                                                   line_no,  
                                                   order_no, 
                                                   diff_1,     
                                                   diff_3,
                                                   profoma_no  
                                           )
                                         )
                                       )
                                      ).extract('/*')
                                      
                                      FROM DUAL;
                                                      
      ------------------------------------------------------------------------------------------------
    
    Expected:
    --------------------------------------
    -- <size_b> should contain all the <size_d> tags as posted earlier     : could not post due to space constraint
  • 3. Re: Handling multilevel subqueries in XMLAGG
    odie_63 Guru
    Currently Being Moderated
    Thanks for the data, that's very helpful.

    An additional question :

    PROFOMA_NO is in the CT_ORDSKU_EXTN table, but you want it at the header level (order_h).

    What happens if there are different PROFOMA_NO for a given order? Is it possible?


    Actually, could you also explain the grouping logic? It's not very intuitive (to say the least).
    Thanks.

    Edited by: odie_63 on 25 juil. 2012 18:09
  • 4. Re: Handling multilevel subqueries in XMLAGG
    Kunal Newbie
    Currently Being Moderated
    There will be only one PROFOMA_NO for one ORDER_H.

    Here is the scenario:
    One PO can have many details. Where each detail is classified by one 'COLOR_CODE' appearing in the <order_d> tag. Each 'COLOR_CODE' can have many 'SIZE_CODE' appearing in the <size_d> tag. The tag <size>, <size_b> and <size_d> are linked together by the 'ASSOC_ID'.

    So i am just grouping them by order then the color within the order and then the sizes within the colors.

    One more question i had. The table 'ct_ordsku_extn' has a field called 'PUBLISH_IND' which is by default 'N'. I want to update the field to 'Y' for all the order_no that my query selects. Is there any way to do it in the query?

    Thanks a ton for your patience.
    -Kunal.
  • 5. Re: Handling multilevel subqueries in XMLAGG
    odie_63 Guru
    Currently Being Moderated
    The tag <size>, <size_b> and <size_d> are linked together by the 'ASSOC_ID'.
    Is it possible to have multiple <size> under <order_d>, and multiple <size_b> under <size>?
    If so, what's the grouping logic?


    The fact that each table does not strictly represent one level of aggregation in the final XML makes it difficult to understand.
    My first assumption was wrong, indeed you have to flatten before grouping.

    This one should be close :
    SELECT XMLElement("order",
             XMLAgg(
               XMLElement("order_h",
                 XMLAttributes(
                   owner      as "owner"
                 , order_no   as "order_no"
                 , profoma_no as "profoma_no"
                 )
               , XMLAgg("ORDER_D_XML")
               )
             )
           )
    FROM (
      SELECT owner
           , order_no
           , profoma_no       
           , XMLElement("order_d",
               XMLAttributes(
                 owner       as "owner"
               , item_parent as "item_no"
               , line_no     as "line_no"
               , order_no    as "order_no"
               , diff_1      as "color_code"
               , diff_3      as "manufacturer"
               )
             , XMLAgg("SIZE_XML")
             ) as "ORDER_D_XML"
      FROM (
        SELECT order_no
             , owner
             , item_parent
             , line_no
             , diff_1
             , diff_3
             , profoma_no
             , XMLElement("size",
                 XMLAttributes(
                   assoc_id   as "assoc_id"
                 , match_01   as "match_01"
                 , match_id   as "match_id"
                 , pack_code  as "pack_code"
                 , size_range as "size_range"
                 , model_name as "model_name"
                 , size_id    as "size_id"
                 )
               , XMLAgg("SIZE_B_XML")
               ) as "SIZE_XML"
        FROM (
          SELECT oh.order_no
               , oh.owner
               , im.item_parent
               , os.line_no
               , im.diff_1
               , im.diff_3
               , os.profoma_no
               , os.assoc_id
               , os.match_01
               , os.match_id
               , os.pack_code
               , os.size_range
               , os.model_name
               , os.size_id
               , XMLElement("Size_b",
                   XMLAttributes(
                     os.assoc_id  as "assoc_id"
                   , os.row_no    as "row_no"
                   , os.total_pct as "total_pct"
                   , os.priority  as "priority"
                   , os.ship_pack as "ship_pack"
                   )
                 , XMLAgg(
                     XMLElement("size_d",
                       XMLAttributes(
                         os.assoc_id       as "assoc_id"
                       , os.ship_pack      as "ship_pack"
                       , os.item           as "ctn_sku"
                       , im.diff_2         as "size_code"
                       , os.alloc_qty      as "alloc_qty"
                       , os.plan_pct_ratio as "plan_pct_ratio"
                       , os.row_no         as "row_no"
                       , os.sort_value     as "sort_value"
                       )
                     ) 
                   )
                 ) as "SIZE_B_XML"
          FROM ct_ordsku_extn os
               JOIN ct_ordhead_extn oh ON os.order_no = oh.order_no
               JOIN item_master im     ON os.item = im.item                                                 
          GROUP BY oh.order_no
                 , oh.owner
                 , im.item_parent
                 , os.line_no
                 , im.diff_1
                 , im.diff_3
                 , os.assoc_id
                 , os.row_no
                 , os.size_id
                 , os.model_name
                 , os.size_range
                 , os.pack_code 
                 , os.profoma_no
                 , os.priority
                 , os.ship_pack
                 , os.total_pct
                 , os.match_id
                 , os.match_01
        )
        GROUP BY order_no
               , owner
               , item_parent
               , line_no
               , diff_1
               , diff_3
               , profoma_no
               , assoc_id
               , match_01
               , match_id
               , pack_code
               , size_range
               , model_name
               , size_id
      )
      GROUP BY order_no
             , owner
             , item_parent
             , line_no
             , diff_1
             , diff_3
             , profoma_no
    )
    GROUP BY owner
           , order_no
           , profoma_no
    ;
    Edited by: odie_63 on 26 juil. 2012 14:23
  • 6. Re: Handling multilevel subqueries in XMLAGG
    Kunal Newbie
    Currently Being Moderated
    Thanks a ton Odie! :-)

    You correctly pointed out..

    1) there can be multiple <size> under <order_d> which should be grouped by the field 'size_range' in the <size> tag.
    2)there cannot be multiple <size_b> under <size>

    one last question :-) . Can i fit the grouping logic of <size> under the tag <order_d> in the query you provided.
    Other wise your query works like a charm!

    Many Thanks,
    Kunal.
  • 7. Re: Handling multilevel subqueries in XMLAGG
    odie_63 Guru
    Currently Being Moderated
    1) there can be multiple <size> under <order_d> which should be grouped by the field 'size_range' in the <size> tag.
    Could you give some additional records with this situation (different size_range values)?
  • 8. Re: Handling multilevel subqueries in XMLAGG
    Kunal Newbie
    Currently Being Moderated
    Hi Odie,

    Here are few additional records for grouping the size tag. You can use these records in tandem with the one which i had posted earlier.

    Thanks,
    Kunal
    Insert into CUSTOM.ITEM_MASTER
       (ITEM, ITEM_NUMBER_TYPE, FORMAT_ID, PREFIX, ITEM_PARENT, 
        ITEM_GRANDPARENT, PACK_IND, ITEM_LEVEL, TRAN_LEVEL, ITEM_AGGREGATE_IND, 
        DIFF_1, DIFF_1_AGGREGATE_IND, DIFF_2, DIFF_2_AGGREGATE_IND, DIFF_3, 
        DIFF_3_AGGREGATE_IND, DIFF_4, DIFF_4_AGGREGATE_IND, DEPT, CLASS, 
        SUBCLASS, STATUS, ITEM_DESC, ITEM_DESC_SECONDARY, SHORT_DESC, 
        DESC_UP, PRIMARY_REF_ITEM_IND, RETAIL_ZONE_GROUP_ID, COST_ZONE_GROUP_ID, STANDARD_UOM, 
        UOM_CONV_FACTOR, PACKAGE_SIZE, PACKAGE_UOM, MERCHANDISE_IND, STORE_ORD_MULT, 
        FORECAST_IND, ORIGINAL_RETAIL, MFG_REC_RETAIL, RETAIL_LABEL_TYPE, RETAIL_LABEL_VALUE, 
        HANDLING_TEMP, HANDLING_SENSITIVITY, CATCH_WEIGHT_IND, WASTE_TYPE, WASTE_PCT, 
        DEFAULT_WASTE_PCT, CONST_DIMEN_IND, SIMPLE_PACK_IND, CONTAINS_INNER_IND, SELLABLE_IND, 
        ORDERABLE_IND, PACK_TYPE, ORDER_AS_TYPE, COMMENTS, ITEM_SERVICE_LEVEL, 
        GIFT_WRAP_IND, SHIP_ALONE_IND, CREATE_DATETIME, LAST_UPDATE_ID, LAST_UPDATE_DATETIME, 
        CHECK_UDA_IND, ITEM_XFORM_IND, INVENTORY_IND, ORDER_TYPE, SALE_TYPE, 
        DEPOSIT_ITEM_TYPE, CONTAINER_ITEM, DEPOSIT_IN_PRICE_PER_UOM, AIP_CASE_TYPE, BANDED_ITEM_IND, 
        CATCH_WEIGHT_TYPE, PERISHABLE_IND, SOH_INQUIRY_AT_PACK_IND, NOTIONAL_PACK_IND, CATCH_WEIGHT_UOM)
     Values
       ('102526829', 'ITEM', NULL, NULL, '6534580', 
        NULL, 'N', 2, 2, 'N', 
        '009', 'N', 'XL', 'N', '102218', 
        'N', 'CA', 'N', 504, 43, 
        1, 'A', 'NEW PANT', 'NOUVEAU PANTALON', 'NEW PANT', 
        'NEW PANT', 'N', 1, 1000, 'EA', 
        NULL, NULL, NULL, 'Y', 'E', 
        'N', NULL, NULL, NULL, NULL, 
        NULL, NULL, 'N', NULL, NULL, 
        NULL, 'N', 'N', 'N', 'Y', 
        'Y', NULL, NULL, NULL, NULL, 
        'N', 'N', TO_DATE('05/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'KGUPTA_RMS', TO_DATE('07/19/2012 10:36:42', 'MM/DD/YYYY HH24:MI:SS'), 
        'N', 'N', 'Y', NULL, NULL, 
        NULL, NULL, NULL, NULL, NULL, 
        NULL, 'N', 'N', 'N', NULL);
    COMMIT;
    
    
    
    Insert into CUSTOM.CT_ORDSKU_EXTN
       (ASSOC_ID, LINE_NO, ORDER_NO, ITEM, SIZE_CODE, 
        ALLOC_QTY, SIZE_ID, MODEL_NAME, SIZE_RANGE, PACK_CODE, 
        MATCH_ID, MATCH_01, SHIP_PACK, PRIORITY, TOTAL_PCT, 
        PCT_RATIO, ROW_NO, PLAN_PCT_RATIO, SORT_VALUE, SELLING_CHANNEL, 
        LADING_POINT, DC_DATE, SHIP_METHOD, FOB_POINT, DELIVERY_TERMS, 
        PROFOMA_NO, ORDER_DESCRIPTION, STORE_TYPE, FOLD_CODE, UNIT_ELC, 
        QTY_PER_INNER, SUPP_OFFER_NO, PUBLISH_IND)
     Values
       (2405, 1, 400020, '102526829', 'XL', 
        200, 'SIZE SCALE 3', 'SIZE SCALE 3', '3', 'BULK', 
        '14', NULL, 'BULK', 'BULK', 100, 
        NULL, 0, 28.2, NULL, 'GRG', 
        NULL, TO_DATE('11/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OCN', '57035', 'FOB', 
        409536, 'LUREX STRIPED CAMI', 'FLOW', 'BASIC', 3.09, 
        1, 22713, 'N');
        
        COMMIT;
  • 9. Re: Handling multilevel subqueries in XMLAGG
    odie_63 Guru
    Currently Being Moderated
    Please see if this one is OK :
    SELECT XMLElement("order",
             XMLAgg(
               XMLElement("order_h",
                 XMLAttributes(
                   owner      as "owner"
                 , order_no   as "order_no"
                 , profoma_no as "profoma_no"
                 )
               , XMLAgg("ORDER_D_XML")
               )
             )
           )
    FROM (
      SELECT owner
           , order_no
           , profoma_no       
           , XMLElement("order_d",
               XMLAttributes(
                 owner       as "owner"
               , item_parent as "item_no"
               , line_no     as "line_no"
               , order_no    as "order_no"
               , diff_1      as "color_code"
               , diff_3      as "manufacturer"
               )
             , XMLAgg("SIZE_XML")
             ) as "ORDER_D_XML"
      FROM (
        SELECT oh.order_no
             , oh.owner
             , im.item_parent
             , os.line_no
             , im.diff_1
             , im.diff_3
             , os.profoma_no
             , XMLElement("size",
                 XMLAttributes(
                   assoc_id   as "assoc_id"
                 , match_01   as "match_01"
                 , match_id   as "match_id"
                 , pack_code  as "pack_code"
                 , size_range as "size_range"
                 , model_name as "model_name"
                 , size_id    as "size_id"
                 )  
               , XMLElement("Size_b",
                   XMLAttributes(
                     os.assoc_id  as "assoc_id"
                   , os.row_no    as "row_no"
                   , os.total_pct as "total_pct"
                   , os.priority  as "priority"
                   , os.ship_pack as "ship_pack"
                   )
                 , XMLAgg(
                     XMLElement("size_d",
                       XMLAttributes(
                         os.assoc_id       as "assoc_id"
                       , os.ship_pack      as "ship_pack"
                       , os.item           as "ctn_sku"
                       , im.diff_2         as "size_code"
                       , os.alloc_qty      as "alloc_qty"
                       , os.plan_pct_ratio as "plan_pct_ratio"
                       , os.row_no         as "row_no"
                       , os.sort_value     as "sort_value"
                       )
                     ) 
                   )
                 ) 
               ) as "SIZE_XML"
        FROM ct_ordsku_extn os
             JOIN ct_ordhead_extn oh ON os.order_no = oh.order_no
             JOIN item_master im     ON os.item = im.item                                                 
        GROUP BY oh.order_no
               , oh.owner
               , im.item_parent
               , os.line_no
               , im.diff_1
               , im.diff_3
               , os.assoc_id
               , os.row_no
               , os.size_id
               , os.model_name
               , os.size_range
               , os.pack_code 
               , os.profoma_no
               , os.priority
               , os.ship_pack
               , os.total_pct
               , os.match_id
               , os.match_01
      )
      GROUP BY order_no
             , owner
             , item_parent
             , line_no
             , diff_1
             , diff_3
             , profoma_no
    )
    GROUP BY owner
           , order_no
           , profoma_no
    ;           
  • 10. Re: Handling multilevel subqueries in XMLAGG
    Kunal Newbie
    Currently Being Moderated
    Hi Odie,

    Its working as a charm. Thanks a lot for your help :-)

    Thanks,
    Kunal.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points