ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00283: document encoding is UTF-8-based but default input encoding is not
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 295
ORA-06512: at line 1

  <?xml version="1.0" encoding="utf-8" ?> 

CREATE TABLE STG_PBKAFFCATALOGSTDSET
(
  AFFILIATENETWORKID   VARCHAR2(20 BYTE),
  AFFILIATEMERCHANTID  VARCHAR2(255 BYTE),
  FEEDFILENAME         VARCHAR2(255 BYTE),
  PRODUCTNAME          VARCHAR2(255 BYTE),
  PRODUCTDESCRIPTION   VARCHAR2(2000 BYTE),
  PRODUCTSKU           VARCHAR2(255 BYTE),
  PRODUCTID            VARCHAR2(20 BYTE),
  PRODUCTLINK          VARCHAR2(255 BYTE),
  CATEGORYID           VARCHAR2(20 BYTE),
  CATEGORYNAME         VARCHAR2(255 BYTE),
  CATEGORYPATH         VARCHAR2(255 BYTE),
  CURRENCY             VARCHAR2(20 BYTE),
  MRP                  VARCHAR2(20 BYTE),
  OFFERPRICE           VARCHAR2(20 BYTE),
  DISCOUNT             VARCHAR2(1500 BYTE),
  DISCOUNT_PERCENTAGE  VARCHAR2(20 BYTE),
  SMALLIMAGEURL        VARCHAR2(1500 BYTE),
  MEDIUMIMAGEURL       VARCHAR2(1500 BYTE),
  LARGEIMAGEURL        VARCHAR2(1500 BYTE),
  FEATURESID           VARCHAR2(20 BYTE),
  FEATURES             VARCHAR2(25 BYTE),
  ISAVAILABLE          VARCHAR2(255 BYTE),
  SHIPPINGCOST         VARCHAR2(20 BYTE),
  SHIPPINGTIME         VARCHAR2(255 BYTE),
  LOCATION             VARCHAR2(255 BYTE),
  DIMENSIONS           VARCHAR2(255 BYTE),
  MANUFACTURER         VARCHAR2(255 BYTE),
  BRANDNAME            VARCHAR2(255 BYTE),
  ISFEATURED           VARCHAR2(20 BYTE),
  MODELNUMBER          VARCHAR2(255 BYTE),
  MERCHANTCATALOG      VARCHAR2(255 BYTE),
  STARTDATE            VARCHAR2(255 BYTE),
  EXPIRATIONDATE       VARCHAR2(255 BYTE)
)

PROCEDURE xml_load(p_tag varchar2,p_xml_file varchar2,p_path varchar2)
   AS
  
   
BEGIN

EXECUTE IMMEDIATE  'CREATE OR REPLACE DIRECTORY TEST_DIR1 AS '''||replace(p_path,'''','''''')||'''';

INSERT INTO stg_PbkAffCatalogStdSet(
  affiliatenetworkid, affiliatemerchantid
,feedfilename,productname,productdescription,productsku,productid,productlink,currency,mrp,offerprice,discount,discount_percentage,smallimageurl,mediumimageurl,largeimageurl,
  featuresid, features,isavailable, shippingcost,shippingtime,LOCATION,dimensions,manufacturer,startdate,expirationdate,categoryid,
  categoryname,categorypath)
select p_tag,y1.affiliatemerchantid,
p_xml_file,y1.productname,y1.productdescription,y1.productsku,y1.productid,y1.productlink,y1.currency,y1.mrp,y1.offerprice,y1.discount,y1.discount_percentage,y1.smallimageurl,y1.mediumimageurl,y1.largeimageurl,y1.featuresid,y1.features,
y1.isavailable,y1.shippingcost,y1.shippingtime,y1.location,y1.dimensions,y1.manufacturer,y1.startdate,y1.expirationdate,
y2.categoryid,y2.categoryname,y2.categorypath

FROM xmltable('ProductFeed/Products/Product'
           passing xmltype(bfilename('TEST_DIR1', p_xml_file ), nls_charset_id('CHAR_CS'))
COLUMNS 
  affiliatenetworkid   VARCHAR2(20) path 'affiliatenetworkid',
  affiliatemerchantid  VARCHAR2(255 ) path 'affilatemerchant',
  feedfilename       VARCHAR2(255 BYTE) path 'feed_file_name',
  productname          VARCHAR2(255 BYTE) path 'title',
  productdescription   VARCHAR2(2000 BYTE) path 'Description',
  productsku           VARCHAR2(255 BYTE) path 'SKU',
  productid            VARCHAR2(20 BYTE) path 'ProductID',
  productlink          VARCHAR2(255 BYTE) path 'link',
  categoryid           VARCHAR2(20 BYTE) path 'categoryid',
  categoryname         VARCHAR2(255 BYTE) path 'category',
  categorypath         VARCHAR2(255 BYTE) path 'categorypath',
  currency             VARCHAR2(20 BYTE) path 'Currency',
  mrp                  VARCHAR2(20 BYTE) path 'mrp',
  offerprice           VARCHAR2(20 BYTE) path 'Price',
  discount             VARCHAR2(1500 BYTE) path 'discount',
  discount_percentage  VARCHAR2(20 BYTE) path 'discount_percentage',
  smallimageurl        VARCHAR2(1500 BYTE) path 'SmallImage',
  mediumimageurl       VARCHAR2(1500 BYTE) path 'MediumImage',
  largeimageurl        VARCHAR2(1500 BYTE) path 'LargeImage',
  featuresid           VARCHAR2(20 BYTE) path 'featuresid',
  features             VARCHAR2(25 BYTE) path 'features',
  isavailable          VARCHAR2(255 BYTE) path 'available',
  shippingcost         VARCHAR2(20 BYTE) path 'shippingcost',
  shippingtime         VARCHAR2(255 BYTE) path 'shippingtime',
  location             VARCHAR2(255 BYTE) path 'location',
  dimensions           VARCHAR2(255 BYTE) path 'dimensions',
  manufacturer         VARCHAR2(255 BYTE) path 'mfg',
  brandname            VARCHAR2(255 BYTE) path 'brandname',
  isfeatured           VARCHAR2(20 BYTE) path 'isfeatured',
  modelnumber          VARCHAR2(255 BYTE) path 'modelnumber',
  merchantcatalog      VARCHAR2(255 BYTE) path 'Merchant',
  startdate            VARCHAR2(255 BYTE) path 'StartDate',
  expirationdate       VARCHAR2(255 BYTE) path 'ExpirationDate',
  Categories           xmltype path  'Categories') y1,
  xmltable('/Categories/Category' passing y1.Categories
  columns
categoryid           VARCHAR2(20 BYTE) path 'CategoryId',
  categoryname         VARCHAR2(255 BYTE) path 'Name',
  categorypath         VARCHAR2(255 BYTE) path 'Path'
  ) y2;

  COMMIT;
  
END;

SELECT VALUE
  FROM SYS.nls_database_parameters
 WHERE parameter = 'NLS_CHARACTERSET'

<?xml version="1.0" encoding="WE8ISO8859P1"?>