Haz
30
2015

DBMS_XMLPARSER standart paketi ile XML verisinin Ayrıştırılması

Merhaba,

Aşağıda DBMS_XMLPARSER paketi ile XML verisinin ayrıştırılması ile ilgili örnek bir script bulabilirsiniz.

Tşk..

 
 
DECLARE
--******************************************************************************************
   SUBTYPE generic_column IS VARCHAR2 (1000);
 
   TYPE headers IS RECORD (
      order_number                   generic_column,
      merchant_order                 generic_column,
      order_date                     generic_column,
      priority                       generic_column,
      note_for_invoice               generic_column,
      note_for_waybill               generic_column,
      note_for_warehouse             generic_column,
      currency_code                  generic_column,
      order_total                    generic_column,
      order_bonus_total              generic_column,
      customer_webno                 generic_column,
      customer_name                  generic_column,
      delivery_person                generic_column,
      customer_type                  generic_column,
      send_bill_to_current_address   generic_column,
      customer_national_id           generic_column,
      bill_to_customer_tax_office    generic_column,
      bill_to_companyname            generic_column,
      bill_to_companyinvoice         generic_column,
      customer_work_phone            generic_column,
      customer_mobile_phone          generic_column,
      customer_email                 generic_column,
      bill_to_address_webno          generic_column,
      bill_to_address_type           generic_column,
      bill_to_address_line1          generic_column,
      bill_to_address_postal_code    generic_column,
      bill_to_address_town           generic_column,
      bill_to_address_district       generic_column,
      bill_to_address_city           generic_column,
      bill_to_address_country        generic_column,
      ship_to_customer_name          generic_column,
      ship_to_address_webno          generic_column,
      ship_to_address_type           generic_column,
      ship_to_address_line1          generic_column,
      ship_to_address_postal_code    generic_column,
      ship_to_address_town           generic_column,
      ship_to_address_district       generic_column,
      ship_to_address_city           generic_column,
      ship_to_address_country        generic_column,
      ship_to_address_description    generic_column,
      delivery_company               generic_column,
      delivery_cost                  generic_column,
      customer_bank_code             generic_column,
      pos_bank_code                  generic_column,
      bank_authorization             generic_column,
      transaction_id                 generic_column,
      installment_flag               generic_column,
      installment                    generic_column,
      advance_received               generic_column,
      fraud_flag                     generic_column,
      payments_recieved_date         generic_column,
      is3d                           generic_column,
      payment_channel                generic_column,
      integration_order_id           generic_column,
      integration_order_number       generic_column
   );
 
--******************************************************************************************
   TYPE line IS RECORD (
      order_number                    generic_column,
      consignment_number              generic_column,
      line_number                     generic_column,
      merchant_line                   generic_column,
      product_code                    generic_column,
      product_description             generic_column,
      barcode                         generic_column,
      tax_rate                        generic_column,
      selling_price                   generic_column,
      quantity                        generic_column,
      uom_code                        generic_column,
      amount                          generic_column,
      crossdock_flag                  generic_column,
      vendor_id                       generic_column,
      vendor_site_id                  generic_column,
      supplier_product_code           generic_column,
      purchasing_price                generic_column,
      purchasing_currency_code        generic_column,
      desi                            generic_column,
      hediye_notu                     generic_column,
      hediye_paketi                   generic_column,
      kampanya_notu1                  generic_column,
      kampanya_notu2                  generic_column,
      kampanya_notu3                  generic_column,
      depocu_notu                     generic_column,
      line_type                       generic_column,
      reference_item_line_number      generic_column,
      integration_discount_amount     generic_column,
      integration_kliksa_dis_amount   generic_column,
      integration_installment_chrg    generic_column,
      integration_commision_amount    generic_column,
      original_price                  generic_column,
      integration_line_id             generic_column,
      integration_product_code        generic_column,
      special_price_type              generic_column,
      special_price_code              generic_column,
      delivery_company                generic_column,
      delivery_type                   generic_column,
      campaign_id                     generic_column,
      paidwithgiftcheque              generic_column,
      drop_shipment                   generic_column,
      delivery_line_cost              generic_column,
      gw_amount                       generic_column,
      gw_quantity                     generic_column,
      parent_product_code             generic_column,
      parent_product_name             generic_column
   );
 
--******************************************************************************************
   TYPE lines IS TABLE OF line
      INDEX BY PLS_INTEGER;
 
--******************************************************************************************
   l_xml_notfound   EXCEPTION;
   l_parser         DBMS_XMLPARSER.parser;
   l_xml_doc        DBMS_XMLDOM.domdocument;
   l_headers        headers;
   l_lines          lines;
   headers_node     DBMS_XMLDOM.domnodelist;
   header_node      DBMS_XMLDOM.domnode;
--******************************************************************************************
   lines_node       DBMS_XMLDOM.domnodelist;
   line_node        DBMS_XMLDOM.domnode;
   j                INT                     := 1;
--******************************************************************************************
   l_xml            CLOB
      := '<?xml version="1.0" encoding="UTF-8"?>
<ORDERHEADER>
       <ORDER_NUMBER>10278631</ORDER_NUMBER>
       <MERCHANT_ORDER>N</MERCHANT_ORDER>
       <ORDER_DATE>2015-06-26</ORDER_DATE>
       <PRIORITY>99</PRIORITY>
       <NOTE_FOR_INVOICE></NOTE_FOR_INVOICE>
       <NOTE_FOR_WAYBILL></NOTE_FOR_WAYBILL>
       <NOTE_FOR_WAREHOUSE></NOTE_FOR_WAREHOUSE>
       <CURRENCY_CODE>TRY</CURRENCY_CODE>
       <ORDER_TOTAL>372.90</ORDER_TOTAL>
       <ORDER_BONUS_TOTAL>0.00</ORDER_BONUS_TOTAL>
       <CUSTOMER_WEBNO>akaraalioglu@kliksa.com</CUSTOMER_WEBNO>
       <CUSTOMER_NAME>A d V s</CUSTOMER_NAME>
       <DELIVERY_PERSON>aaarz AAA</DELIVERY_PERSON>
       <CUSTOMER_TYPE>Kliksa</CUSTOMER_TYPE>
       <SEND_BILL_TO_CURRENT_ADDRESS></SEND_BILL_TO_CURRENT_ADDRESS>
       <CUSTOMER_NATIONAL_ID>11111111111</CUSTOMER_NATIONAL_ID>
       <BILL_TO_CUSTOMER_TAX_OFFICE></BILL_TO_CUSTOMER_TAX_OFFICE>
       <BILL_TO_COMPANYNAME>arzu aaaaaaa</BILL_TO_COMPANYNAME>
       <BILL_TO_COMPANYINVOICE></BILL_TO_COMPANYINVOICE>
       <CUSTOMER_WORK_PHONE>5</CUSTOMER_WORK_PHONE>
       <CUSTOMER_MOBILE_PHONE>5</CUSTOMER_MOBILE_PHONE>
       <CUSTOMER_EMAIL>akaraalioglu@kliksa.com</CUSTOMER_EMAIL>
       <BILL_TO_ADDRESS_WEBNO>9038976876567</BILL_TO_ADDRESS_WEBNO>
       <BILL_TO_ADDRESS_TYPE>Fatura</BILL_TO_ADDRESS_TYPE>
       <BILL_TO_ADDRESS_LINE1>aaa</BILL_TO_ADDRESS_LINE1>
       <BILL_TO_ADDRESS_LINE2></BILL_TO_ADDRESS_LINE2>
       <BILL_TO_ADDRESS_POSTAL_CODE></BILL_TO_ADDRESS_POSTAL_CODE>
       <BILL_TO_ADDRESS_TOWN>ALADAĞ</BILL_TO_ADDRESS_TOWN>
       <BILL_TO_ADDRESS_DISTRICT>AKÖREN</BILL_TO_ADDRESS_DISTRICT>
       <BILL_TO_ADDRESS_CITY>Adana</BILL_TO_ADDRESS_CITY>
       <BILL_TO_ADDRESS_COUNTRY>TR</BILL_TO_ADDRESS_COUNTRY>
       <SHIP_TO_ADDRESS_WEBNO>9038976909335</SHIP_TO_ADDRESS_WEBNO>
       <SHIP_TO_ADDRESS_TYPE>Teslimat</SHIP_TO_ADDRESS_TYPE>
       <SHIP_TO_ADDRESS_LINE1>adres testti</SHIP_TO_ADDRESS_LINE1>
       <SHIP_TO_ADDRESS_LINE2></SHIP_TO_ADDRESS_LINE2>
       <SHIP_TO_ADDRESS_POSTAL_CODE></SHIP_TO_ADDRESS_POSTAL_CODE>
       <SHIP_TO_ADDRESS_TOWN>ALADAĞ</SHIP_TO_ADDRESS_TOWN>
       <SHIP_TO_ADDRESS_DISTRICT>AKÖREN</SHIP_TO_ADDRESS_DISTRICT>
       <SHIP_TO_ADDRESS_CITY>Adana</SHIP_TO_ADDRESS_CITY>
       <SHIP_TO_ADDRESS_COUNTRY>TR</SHIP_TO_ADDRESS_COUNTRY>
       <SHIP_TO_ADDRESS_DESCRIPTION>adres testti</SHIP_TO_ADDRESS_DESCRIPTION>
       <DELIVERY_COMPANY></DELIVERY_COMPANY>
       <DELIVERY_COST>0.00</DELIVERY_COST>
       <CUSTOMER_BANK_CODE>GARANTI</CUSTOMER_BANK_CODE>
       <POS_BANK_CODE>GARANTI</POS_BANK_CODE>
       <BANK_AUTHORIZATION>MTQzNTMyNjgyMgxX1KmeVrLi4QqiC6f9</BANK_AUTHORIZATION>
       <TRANSACTION_ID>MTQzNTMyNjgyMgxX1KmeVrLi4QqiC6f9</TRANSACTION_ID>
       <INSTALLMENT_FLAG>Y</INSTALLMENT_FLAG>
       <INSTALLMENT>6</INSTALLMENT>
       <ADVANCE_RECEIVED>67.98</ADVANCE_RECEIVED>
       <FRAUD_FLAG>N</FRAUD_FLAG>
       <PAYMENTS_RECIEVED_DATE>2015-06-26</PAYMENTS_RECIEVED_DATE>
       <IS3D>1</IS3D>
       <PAYMENT_CHANNEL></PAYMENT_CHANNEL>
       <INTEGRATION_ORDER_ID></INTEGRATION_ORDER_ID>
       <INTEGRATION_ORDER_NUMBER></INTEGRATION_ORDER_NUMBER>
       <ORDER_LINES>
              <ORDER_LINE>
                     <ORDER_NUMBER>10278631</ORDER_NUMBER>
                     <CONSIGNMENT_NUMBER></CONSIGNMENT_NUMBER>
                     <LINE_NUMBER>1</LINE_NUMBER>
                     <MERCHANT_LINE>N</MERCHANT_LINE>
                     <PRODUCT_CODE>902077253</PRODUCT_CODE>
                     <PRODUCT_DESCRIPTION>Bulova C4102</PRODUCT_DESCRIPTION>
                     <BARCODE>042429500754</BARCODE>
                     <TAX_RATE>18</TAX_RATE>
                     <SELLING_PRICE>372.90</SELLING_PRICE>
                     <QUANTITY>1</QUANTITY>
                     <UOM_CODE>AD</UOM_CODE>
                     <AMOUNT>372.90</AMOUNT>
                     <CROSSDOCK_FLAG>N</CROSSDOCK_FLAG>
                     <VENDOR_ID>838</VENDOR_ID>
                     <VENDOR_SITE_ID>123741</VENDOR_SITE_ID>
                     <SUPPLIER_PRODUCT_CODE>SBU11.13007</SUPPLIER_PRODUCT_CODE>
                     <PURCHASING_PRICE></PURCHASING_PRICE>
                     <PURCHASING_CURRENCY_CODE>TRY</PURCHASING_CURRENCY_CODE>
                     <DESI>1.0</DESI>
                     <HEDIYE_NOTU></HEDIYE_NOTU>
                     <HEDIYE_PAKETI>N</HEDIYE_PAKETI>
                     <KAMPANYA_NOTU1></KAMPANYA_NOTU1>
                     <KAMPANYA_NOTU2></KAMPANYA_NOTU2>
                     <KAMPANYA_NOTU3></KAMPANYA_NOTU3>
                     <DEPOCU_NOTU></DEPOCU_NOTU>
                     <LINE_TYPE>MALZEME</LINE_TYPE>
                     <REFERENCE_ITEM_LINE_NUMBER></REFERENCE_ITEM_LINE_NUMBER>
                     <DELIVERY_COMPANY></DELIVERY_COMPANY>
                     <DELIVERY_TYPE></DELIVERY_TYPE>
                     <CAMPAIGN_ID>1000 liraya 100 lira indirim ibrahim,John_OTFD</CAMPAIGN_ID>
                     <PAIDWITHGIFTCHEQUE></PAIDWITHGIFTCHEQUE>
                     <INTEGRATION_DISCOUNT_AMOUNT></INTEGRATION_DISCOUNT_AMOUNT>
                     <INTEGRATION_INSTALLMENT_CHRG></INTEGRATION_INSTALLMENT_CHRG>
                     <INTEGRATION_COMMISION_AMOUNT></INTEGRATION_COMMISION_AMOUNT>
                     <INTEGRATION_KLIKSA_DISCOUNT_AMOUNT></INTEGRATION_KLIKSA_DISCOUNT_AMOUNT>
                     <INTEGRATION_LINE_ID></INTEGRATION_LINE_ID>
                     <INTEGRATION_PRODUCT_CODE></INTEGRATION_PRODUCT_CODE>
                     <SPECIAL_PRICE_TYPE>OTHER</SPECIAL_PRICE_TYPE>
                     <SPECIAL_PRICE_CODE></SPECIAL_PRICE_CODE>
                     <ORIGINAL_PRICE>407.90</ORIGINAL_PRICE>
                     <DROP_SHIPMENT>N</DROP_SHIPMENT>
                     <DELIVERY_LINE_COST>0.00</DELIVERY_LINE_COST>
                     <PARENT_PRODUCT_CODE></PARENT_PRODUCT_CODE>
                     <PARENT_PRODUCT_NAME></PARENT_PRODUCT_NAME>
                     <GW_QUANTITY></GW_QUANTITY>
                     <GW_AMOUNT></GW_AMOUNT>
                 </ORDER_LINE>
          </ORDER_LINES>
   </ORDERHEADER>';
BEGIN
--***********************************************************************************************
   l_parser := DBMS_XMLPARSER.newparser;
   DBMS_XMLPARSER.parseclob (l_parser, l_xml);
   l_xml_doc := DBMS_XMLPARSER.getdocument (l_parser);
--***********************************************************************************************
   headers_node :=
      DBMS_XSLPROCESSOR.selectnodes (DBMS_XMLDOM.makenode (l_xml_doc),
                                     'ORDERHEADER'
                                    );
 
   FOR i IN 0 .. DBMS_XMLDOM.getlength (headers_node) - 1
   LOOP
      header_node := DBMS_XMLDOM.item (headers_node, i);
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'ORDER_NUMBER/text()',
                                 l_headers.order_number
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'MERCHANT_ORDER/text()',
                                 l_headers.merchant_order
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'ORDER_DATE/text()',
                                 l_headers.order_date
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'PRIORITY/text()',
                                 l_headers.priority
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'NOTE_FOR_INVOICE/text()',
                                 l_headers.note_for_invoice
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'NOTE_FOR_WAYBILL/text()',
                                 l_headers.note_for_waybill
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'NOTE_FOR_WAREHOUSE/text()',
                                 l_headers.note_for_warehouse
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CURRENCY_CODE/text()',
                                 l_headers.currency_code
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'ORDER_TOTAL/text()',
                                 l_headers.order_total
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'ORDER_BONUS_TOTAL/text()',
                                 l_headers.order_bonus_total
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CUSTOMER_WEBNO/text()',
                                 l_headers.customer_webno
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CUSTOMER_NAME/text()',
                                 l_headers.customer_name
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'DELIVERY_PERSON/text()',
                                 l_headers.delivery_person
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CUSTOMER_TYPE/text()',
                                 l_headers.customer_type
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SEND_BILL_TO_CURRENT_ADDRESS/text()',
                                 l_headers.send_bill_to_current_address
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CUSTOMER_NATIONAL_ID/text()',
                                 l_headers.customer_national_id
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_CUSTOMER_TAX_OFFICE/text()',
                                 l_headers.bill_to_customer_tax_office
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_COMPANYNAME/text()',
                                 l_headers.bill_to_companyname
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_COMPANYINVOICE/text()',
                                 l_headers.bill_to_companyinvoice
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CUSTOMER_WORK_PHONE/text()',
                                 l_headers.customer_work_phone
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CUSTOMER_EMAIL/text()',
                                 l_headers.send_bill_to_current_address
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_WEBNO/text()',
                                 l_headers.bill_to_address_webno
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_TYPE/text()',
                                 l_headers.bill_to_address_type
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_LINE1/text()',
                                 l_headers.bill_to_address_line1
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_POSTAL_CODE/text()',
                                 l_headers.bill_to_address_postal_code
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_TOWN/text()',
                                 l_headers.bill_to_address_town
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_TOWN/text()',
                                 l_headers.bill_to_address_town
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_POSTAL_CODE/text()',
                                 l_headers.bill_to_address_postal_code
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_DISTRICT/text()',
                                 l_headers.bill_to_address_district
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_CITY/text()',
                                 l_headers.bill_to_address_city
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BILL_TO_ADDRESS_COUNTRY/text()',
                                 l_headers.bill_to_address_country
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_CUSTOMER_NAME/text()',
                                 l_headers.ship_to_customer_name
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_WEBNO/text()',
                                 l_headers.ship_to_address_webno
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_TYPE/text()',
                                 l_headers.ship_to_address_type
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_LINE1/text()',
                                 l_headers.ship_to_address_line1
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_POSTAL_CODE/text()',
                                 l_headers.ship_to_address_postal_code
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_TOWN/text()',
                                 l_headers.ship_to_address_town
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_DISTRICT/text()',
                                 l_headers.ship_to_address_district
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_CITY/text()',
                                 l_headers.ship_to_address_city
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_COUNTRY/text()',
                                 l_headers.ship_to_address_country
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'SHIP_TO_ADDRESS_DESCRIPTION/text()',
                                 l_headers.ship_to_address_description
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'DELIVERY_COMPANY/text()',
                                 l_headers.delivery_company
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'DELIVERY_COST/text()',
                                 l_headers.delivery_cost
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'CUSTOMER_BANK_CODE/text()',
                                 l_headers.customer_bank_code
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'POS_BANK_CODE/text()',
                                 l_headers.pos_bank_code
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'BANK_AUTHORIZATION/text()',
                                 l_headers.bank_authorization
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'TRANSACTION_ID/text()',
                                 l_headers.transaction_id
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'INSTALLMENT_FLAG/text()',
                                 l_headers.installment_flag
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'INSTALLMENT/text()',
                                 l_headers.installment
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'ADVANCE_RECEIVED/text()',
                                 l_headers.advance_received
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'FRAUD_FLAG/text()',
                                 l_headers.fraud_flag
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'PAYMENTS_RECIEVED_DATE/text()',
                                 l_headers.payments_recieved_date
                                );
      DBMS_XSLPROCESSOR.valueof (header_node, 'IS3D/text()', l_headers.is3d);
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'PAYMENT_CHANNEL/text()',
                                 l_headers.payment_channel
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'INTEGRATION_ORDER_ID/text()',
                                 l_headers.integration_order_id
                                );
      DBMS_XSLPROCESSOR.valueof (header_node,
                                 'INTEGRATION_ORDER_NUMBER/text()',
                                 l_headers.integration_order_number
                                );
   END LOOP;
 
--***********************************************************************************************
   lines_node :=
      DBMS_XSLPROCESSOR.selectnodes (DBMS_XMLDOM.makenode (l_xml_doc),
                                     'ORDERHEADER/ORDER_LINES/ORDER_LINE'
                                    );
 
   FOR i IN 0 .. DBMS_XMLDOM.getlength (lines_node) - 1
   LOOP
      line_node := DBMS_XMLDOM.item (lines_node, i);
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'ORDER_NUMBER/text()',
                                 l_lines (j).order_number
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'CONSIGNMENT_NUMBER/text()',
                                 l_lines (j).consignment_number
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'LINE_NUMBER/text()',
                                 l_lines (j).line_number
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'MERCHANT_LINE/text()',
                                 l_lines (j).merchant_line
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'PRODUCT_CODE/text()',
                                 l_lines (j).product_code
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'PRODUCT_DESCRIPTION/text()',
                                 l_lines (j).product_description
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'BARCODE/text()',
                                 l_lines (j).barcode
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'TAX_RATE/text()',
                                 l_lines (j).tax_rate
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'SELLING_PRICE/text()',
                                 l_lines (j).selling_price
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'QUANTITY/text()',
                                 l_lines (j).quantity
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'UOM_CODE/text()',
                                 l_lines (j).uom_code
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'AMOUNT/text()',
                                 l_lines (j).amount
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'CROSSDOCK_FLAG/text()',
                                 l_lines (j).crossdock_flag
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'VENDOR_ID/text()',
                                 l_lines (j).vendor_id
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'VENDOR_SITE_ID/text()',
                                 l_lines (j).vendor_site_id
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'SUPPLIER_PRODUCT_CODE/text()',
                                 l_lines (j).supplier_product_code
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'PURCHASING_PRICE/text()',
                                 l_lines (j).purchasing_price
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'PURCHASING_CURRENCY_CODE/text()',
                                 l_lines (j).purchasing_currency_code
                                );
      DBMS_XSLPROCESSOR.valueof (line_node, 'DESI/text()', l_lines (j).desi);
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'HEDIYE_NOTU/text()',
                                 l_lines (j).hediye_notu
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'HEDIYE_PAKETI/text()',
                                 l_lines (j).hediye_paketi
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'KAMPANYA_NOTU1/text()',
                                 l_lines (j).kampanya_notu1
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'KAMPANYA_NOTU2/text()',
                                 l_lines (j).kampanya_notu2
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'KAMPANYA_NOTU3/text()',
                                 l_lines (j).kampanya_notu3
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'DEPOCU_NOTU/text()',
                                 l_lines (j).depocu_notu
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'LINE_TYPE/text()',
                                 l_lines (j).line_type
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'REFERENCE_ITEM_LINE_NUMBER/text()',
                                 l_lines (j).reference_item_line_number
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'INTEGRATION_DISCOUNT_AMOUNT/text()',
                                 l_lines (j).integration_discount_amount
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'INTEGRATION_KLIKSA_DISCOUNT_AMOUNT/text()',
                                 l_lines (j).integration_kliksa_dis_amount
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'INTEGRATION_INSTALLMENT_CHRG/text()',
                                 l_lines (j).integration_installment_chrg
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'INTEGRATION_COMMISION_AMOUNT/text()',
                                 l_lines (j).integration_commision_amount
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'ORIGINAL_PRICE/text()',
                                 l_lines (j).original_price
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'SPECIAL_PRICE_TYPE/text()',
                                 l_lines (j).special_price_type
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'SPECIAL_PRICE_CODE/text()',
                                 l_lines (j).special_price_code
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'INTEGRATION_LINE_ID/text()',
                                 l_lines (j).integration_line_id
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'INTEGRATION_PRODUCT_CODE/text()',
                                 l_lines (j).integration_product_code
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'DELIVERY_COMPANY/text()',
                                 l_lines (j).delivery_company
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'DELIVERY_TYPE/text()',
                                 l_lines (j).delivery_type
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'CAMPAIGN_ID/text()',
                                 l_lines (j).campaign_id
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'PAIDWITHGIFTCHEQUE/text()',
                                 l_lines (j).paidwithgiftcheque
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'DROP_SHIPMENT/text()',
                                 l_lines (j).drop_shipment
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'DELIVERY_LINE_COST/text()',
                                 l_lines (j).delivery_line_cost
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'GW_AMOUNT/text()',
                                 l_lines (j).gw_amount
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'GW_QUANTITY/text()',
                                 l_lines (j).gw_quantity
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'PARENT_CODE/text()',
                                 l_lines (j).parent_product_code
                                );
      DBMS_XSLPROCESSOR.valueof (line_node,
                                 'PARENT_DESCRIPTION/text()',
                                 l_lines (j).parent_product_name
                                );
      j := j + 1;
   END LOOP;
 
--***********************************************************************************************
/*işlemler tamamlandığında başlık ve satır bilgileri
  l_headers  ve l_lines isimli değişkenlerde veriler toplanır.
*/
   DBMS_OUTPUT.put_line ('başlık ORDER_TOTAL :' || l_headers.order_total);
   DBMS_OUTPUT.put_line (   '1.satır ORIGINAL_PRICE :'
                         || l_lines (1).original_price
                        );
--***********************************************************************************************
END;

Mustafa Korkmaz

Oracle Applications Developer at Partnera
2005 yılında Dokuz Eylül Üniversitesi Endüstri Mühendisliğinden Mezun
Oldu, 2011 Yılında Ahmet Yesevi Üniversitesi Bilgisayar Mühendisliğinde
Yüksek Lisansını Tamamlandı,2012 Yılında Doğuş Üniversitesinde Bilgisayar
Mühendisliği Doktora Eğitimine Başladı.10 yılı aşkın bir zamandır Oracle
Ürünleri Uygulama Yazılım Uzmanı Olarak profesyonel kariyerine devam
etmektedir. İlgi alanları Middleware, CI/CD,
Oracle Database , PL/SQL , Java , Makine Öğrenmesi ve Veri Madenciliği alanlarındadır.
2 versiyon Oracle Database ' (10g, 11g) inde OCA(Oracle Certificate Associate) ve
PL/SQL Certificated Professional ünvanları bulunmaktadır.
Mail: mustafakorkmz@gmail.com
Mustafa Korkmaz


Yorum yapın

*