How to insert XML attributes in oracle tables using XMLTABLE

Question

I have a procedure which will accept XML CLOB data as input parameter. The procedure will load the data in custom table. My problem is, the XML data will have parent and child records. I want to insert the records into single table. I am able to insert parent attribute records but, i was unable to insert child attribute records.

Please help me to achieve this!!!

The details are:

My Procedure:

CREATE OR REPLACE PROCEDURE APPS.XXWSH_SHIPPING_ORDER_ADV_PROC (P_XML_DATA IN CLOB)     
AS      
y CLOB;
BEGIN

y:=P_XML_DATA;
INSERT INTO XXWSH_SHIPPING_ORDER_ADVICE(ORDER_NO,
LINE_NO,
SHIPMENT_NUMBER,
ITEM_NO,
UNIT_OF_MEASURE,
QUANTITY,  
LPN,
TRACKING_ID,
SHIPMENT_COST,
WEIGHT, 
SHIP_DATE,
DELIVERY_DATE ,
CARRIER,  
SERVICE_LEVEL,
SHIP_TO_NAME,
SHIP_TO_NAME2,
SHIP_TO_ADDRESS,
SHIP_TO_ADDRESS2, 
SHIP_TO_CITY,
SHIP_TO_CONTACT ,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE,
SHIP_TO_COUNTRY_CODE,
SHIP_TO_PHONE_NO,
SHIP_TO_FAX_NO  ,
RESIDENTIAL_DELIVERY,
SHIPPING_PAYMENT_TYPE,
THIRD_PARTY_SHIP_ACCOUNT_NO,
FREE_FREIGHT,
PROCESS_FLAG,
SOURCE_NAME,
ERROR_CODE,
ERROR_MESSAGE,
OPERATING_UNIT_NAME,
CREATED_BY_NAME,
LAST_UPDATED_BY_NAME,
LAST_UPDATE_DATE,
CREATION_DATE,
CONTAINER_CODE,
PRO_NUMBER,
CARTON_CREATED_DATE,
CARTON_CREATED_BY,
CARTON_LOCATION_CODE,
VICS_BILL_OF_LADING,
CARTON_MODIFIED_DATE,
CARTON_MODIFIED_BY,
CONSOLIDATION_CODE
)
SELECT  *
FROM  XMLTABLE ( '/WarehouseShippingAdvices/WarehouseShippingAdvice/AdviceLine' passing xmltype (y)
COLUMNS 
ORDER_NO                     NUMBER           path '@OrderNumber',
LINE_NO                      NUMBER           path '@LineNumber[1]',
SHIPMENT_NUMBER              NUMBER           path '@ShipmentNumber',
ITEM_NO                      VARCHAR2(20)     path '@ItemNumber[1]',
UNIT_OF_MEASURE              VARCHAR2(10)     path '@UnitOfMeasure',
QUANTITY                     NUMBER           path '@ItemQuantity[1]',  
LPN                          VARCHAR2(50)     path '@LPN',
TRACKING_ID                  VARCHAR2(40)     path '@TrackingId',
SHIPMENT_COST                NUMBER           path '@ShipmentCost',
WEIGHT                       NUMBER           path '@Weight', 
SHIP_DATE                    DATE             path '@ShipDate',
DELIVERY_DATE                DATE             path '@DeliveryDate',
CARRIER                      VARCHAR2(30)     path '@Carrier',  
SERVICE_LEVEL                VARCHAR2(30)     path '@ServiceLevel',
SHIP_TO_NAME                 VARCHAR2(50)     path '@ShipToName',
SHIP_TO_NAME2                VARCHAR2(50)     path '@ShipToName2',
SHIP_TO_ADDRESS              VARCHAR2(50)     path '@ShipToAddress',
SHIP_TO_ADDRESS2             VARCHAR2(50)     path '@ShipToAddress2', 
SHIP_TO_CITY                 VARCHAR2(30)     path '@ShipToCity',
SHIP_TO_CONTACT              VARCHAR2(50)     path '@ShipToContact',
SHIP_TO_ZIP_CODE             VARCHAR2(20)     path '@ShipToZipCode',
SHIP_TO_STATE                VARCHAR2(30)     path '@ShipToState',
SHIP_TO_COUNTRY_CODE         VARCHAR2(10)     path '@ShipToCountry',
SHIP_TO_PHONE_NO             VARCHAR2(30)     path '@ShipToPhoneNumber',
SHIP_TO_FAX_NO               VARCHAR2(30)     path '@ShipToFaxNumber',
RESIDENTIAL_DELIVERY         VARCHAR2(1)      path '@ResidentialDelivery',
SHIPPING_PAYMENT_TYPE        VARCHAR2(30)     path '@ShippingPaymentType',
THIRD_PARTY_SHIP_ACCOUNT_NO  VARCHAR2(20)     path '@ThirdPartyShipAcctNumber',
FREE_FREIGHT                 VARCHAR2(1)      path '@FreeFleightFlag',
PROCESS_FLAG                 VARCHAR2(10)     path '@ProcessFlag',
SOURCE_NAME                  VARCHAR2(50)     path '@SourceSystem',
ERROR_CODE                   VARCHAR2(50)     path '@ErrorCode',
ERROR_MESSAGE                VARCHAR2(2000)   path '@ErrorMessage',
OPERATING_UNIT_NAME          VARCHAR2(100)    path '@OperatingUnitName',
CREATED_BY_NAME              VARCHAR2(50)     path '@CreatedByName',
LAST_UPDATED_BY_NAME         VARCHAR2(50)     path '@LastUpdatedByName',
LAST_UPDATE_DATE             DATE             path '@LastUpdateDate',
CREATION_DATE                DATE             path '@CreationDate',
--HEADER_ID                  NUMBER           path '@',
CONTAINER_CODE               VARCHAR2(50)     path '@ContainerCode',
PRO_NUMBER                   VARCHAR2(50)     path '@ProNumber',
CARTON_CREATED_DATE          DATE             path '@CartonCreatedDate',
CARTON_CREATED_BY            VARCHAR2(50)     path '@CartonCreatedBy',
CARTON_LOCATION_CODE         VARCHAR2(50)     path '@LocationCode',
VICS_BILL_OF_LADING          VARCHAR2(50)     path '@VICSBillOfLading',
CARTON_MODIFIED_DATE         DATE             path '@CartonModifiedDate',
CARTON_MODIFIED_BY           VARCHAR2(50)      path '@CartonModifiedBy',
CONSOLIDATION_CODE           VARCHAR2(150)    path '@ConsolidationCode'
);

END XXWSH_SHIPPING_ORDER_ADV_PROC;

Procedure Execution script:

DECLARE
  p_xml_data   CLOB;
BEGIN
  p_xml_data :=
    '<WarehouseShippingAdvices>  <WarehouseShippingAdvice OrderNumber="5000008" LPN="" TrackingId="123" ShipmentNumber="12345" ShipmentCost="10" ShipDate="27-FEB-14" DeliveryDate="" Carrier="DHL" Weight="100" ShippingPaymentType="" ServiceLevel="TEST" ShipToName="FCS" ShipToName2="" ShipToAddress="HYD" ShipToAddress2="Miyapur" ShipToCity="HYD" ShipToContact="Sateesh" ShipToZipCode="500049" ShipToState="" ShipToCountry="" ShipToPhoneNumber="" ShipToFaxNumber="" ResidentialDelivery="" ThirdPartyShipAcctNumber="" FreeFleightFlag="" SourceSystem="EBS" ContainerCode="" ProNumber="" CartonCreatedDate="" CartonCreatedBy="" LocationCode="" VICSBillOfLading="" CartonModifiedDate="" CartonModifiedBy="" UnitOfMeasure="EA" ProcessFlag="" OperatingUnitName="" CreatedByName="Sateesh" LastUpdatedByName="Sateesh" LastUpdateDate="" CreationDate="" ConsolidationCode="CC">  <AdviceLine LineNumber="1" ItemNumber="1001" ItemQuantity="10" /> <AdviceLine LineNumber="2" ItemNumber="1002" ItemQuantity="20" />  <AdviceLine LineNumber="3" ItemNumber="1003" ItemQuantity="30" />  <AdviceLine LineNumber="4" ItemNumber="1004" ItemQuantity="40" /> </WarehouseShippingAdvice></WarehouseShippingAdvices>';
  xxwsh_shipping_order_adv_proc (p_xml_data);
END;

XML Data format:

<WarehouseShippingAdvices>  <WarehouseShippingAdvice OrderNumber="5000008" LPN="" TrackingId="123" ShipmentNumber="12345" ShipmentCost="10" ShipDate="27-FEB-14" DeliveryDate="" Carrier="DHL" Weight="100" ShippingPaymentType="" ServiceLevel="TEST" ShipToName="FCS" ShipToName2="" ShipToAddress="HYD" ShipToAddress2="Miyapur" ShipToCity="HYD" ShipToContact="Sateesh" ShipToZipCode="500049" ShipToState="" ShipToCountry="" ShipToPhoneNumber="" ShipToFaxNumber="" ResidentialDelivery="" ThirdPartyShipAcctNumber="" FreeFleightFlag="" SourceSystem="EBS" ContainerCode="" ProNumber="" CartonCreatedDate="" CartonCreatedBy="" LocationCode="" VICSBillOfLading="" CartonModifiedDate="" CartonModifiedBy="" UnitOfMeasure="EA" ProcessFlag="" OperatingUnitName="" CreatedByName="Sateesh" LastUpdatedByName="Sateesh" LastUpdateDate="" CreationDate="" ConsolidationCode="CC">
 <AdviceLine LineNumber="1" ItemNumber="1001" ItemQuantity="10" />
    <AdviceLine LineNumber="2" ItemNumber="1002" ItemQuantity="20" />
    <AdviceLine LineNumber="3" ItemNumber="1003" ItemQuantity="30" />
    <AdviceLine LineNumber="4" ItemNumber="1004" ItemQuantity="40" />
</WarehouseShippingAdvice></WarehouseShippingAdvices>

Here, WarehouseShippingAdvices is the mail tag which contains WarehouseShippingAdvice sub tag for one order number.

Each order will have many lines and the tag is .

I want to insert all the 4 AdviceLine records into my custom table.

Please verify my procedure and let me know where I am doing mistake.

Answer 1

Based on this post, it can be done like this

SELECT *
FROM   XMLTABLE ('for $i in $wh//WarehouseShippingAdvice,
                 $j in $i//AdviceLine
                 return <deNormalizedWH>{$i}{$j}</deNormalizedWH>'
PASSING xmltype('<WarehouseShippingAdvices>  <WarehouseShippingAdvice OrderNumber="5000008" LPN="" TrackingId="123" ShipmentNumber="12345" ShipmentCost="10" ShipDate="27-FEB-14" DeliveryDate="" Carrier="DHL" Weight="100" ShippingPaymentType="" ServiceLevel="TEST" ShipToName="FCS" ShipToName2="" ShipToAddress="HYD" ShipToAddress2="Miyapur" ShipToCity="HYD" ShipToContact="Sateesh" ShipToZipCode="500049" ShipToState="" ShipToCountry="" ShipToPhoneNumber="" ShipToFaxNumber="" ResidentialDelivery="" ThirdPartyShipAcctNumber="" FreeFleightFlag="" SourceSystem="EBS" ContainerCode="" ProNumber="" CartonCreatedDate="" CartonCreatedBy="" LocationCode="" VICSBillOfLading="" CartonModifiedDate="" CartonModifiedBy="" UnitOfMeasure="EA" ProcessFlag="" OperatingUnitName="" CreatedByName="Sateesh" LastUpdatedByName="Sateesh" LastUpdateDate="" CreationDate="" ConsolidationCode="CC">  <AdviceLine LineNumber="1" ItemNumber="1001" ItemQuantity="10" /> <AdviceLine LineNumber="2" ItemNumber="1002" ItemQuantity="20" />  <AdviceLine LineNumber="3" ItemNumber="1003" ItemQuantity="30" />  <AdviceLine LineNumber="4" ItemNumber="1004" ItemQuantity="40" /> </WarehouseShippingAdvice></WarehouseShippingAdvices>')
AS "wh"
COLUMNS ORDER_NO                     number           path 'WarehouseShippingAdvice/@OrderNumber',
        LINE_NO                      NUMBER           path 'AdviceLine/@LineNumber',
        SHIPMENT_NUMBER              NUMBER           path 'WarehouseShippingAdvice/@ShipmentNumber',
        ITEM_NO                      VARCHAR2(20)     path 'AdviceLine/@ItemNumber',
        UNIT_OF_MEASURE              VARCHAR2(10)     path 'WarehouseShippingAdvice/@UnitOfMeasure',
        QUANTITY                     NUMBER           path 'AdviceLine/@ItemQuantity',
        LPN                          VARCHAR2(50)     path 'WarehouseShippingAdvice/@LPN'
) x;

Here is a sqlfiddle demo