Home » SQL & PL/SQL » SQL & PL/SQL » XML Special Character (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
XML Special Character [message #671080] Tue, 14 August 2018 15:07
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi All,

I'm trying to develop a report and facing below error with one Column "Sub_market_type" having some special Characters like '&'

"The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
An invalid character was found in text content. Error processing resource 'https://dvl-ebs.proquest.com/OA_CGI/FNDWRR.exe?t...
<SUB_MARKET_TYPE>Personal &amp; Household Products
ass="e">"

SELECT DECODE (chrb.org_id,  '81', 'US',  '82', 'UK',  '83', 'SPAIN')AS org,
          hca.attribute1 market_type,
          (hca.attribute2) sub_market_type,
          chrb.contract_number,
          cleb.line_number line_number,
          (hca.account_number) ship_account_number,
          ( (SELECT DISTINCT hca.account_name
               FROM apps.hz_locations hl,
                    apps.hz_party_sites hps,
                    apps.hz_cust_accounts hca,
                    apps.hz_cust_acct_sites_all hcas,
                    apps.hz_cust_site_uses_all hcsu
              WHERE     hcsu.site_use_code = 'BILL_TO'
                    AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
                    AND hcas.cust_account_id = hca.cust_account_id
                    AND hcas.party_site_id = hps.party_site_id
                    AND hps.location_id = hl.location_id
                    AND hcsu.site_use_id = chrb.bill_to_site_use_id))
             bill_to_account_name,
          (SELECT DISTINCT hca.account_number
             FROM apps.hz_locations hl,
                  apps.hz_party_sites hps,
                  apps.hz_cust_accounts hca,
                  apps.hz_cust_acct_sites_all hcas,
                  apps.hz_cust_site_uses_all hcsu
            WHERE     hcsu.site_use_code = 'BILL_TO'
                  AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
                  AND hcas.cust_account_id = hca.cust_account_id
                  AND hcas.party_site_id = hps.party_site_id
                  AND hps.location_id = hl.location_id
                  AND hcsu.site_use_id = chrb.bill_to_site_use_id)
             bill_to_account_number,
          (hca.account_name) ship_account_name,
          chrb.cust_po_number_req_yn,
          (hl.address4) site_dept,
          (hl.address1) address1,
          (hl.address2) address2,
          --, (hl.address3) address3, (hl.street) street,
          (hl.city) city,
          DECODE (hl.country, 'CA', hl.province, hl.state) state,
          hl.postal_code,
          hl.country,
          (SELECT geography_name
             FROM apps.hz_geographies
            WHERE country_code = hl.country AND geography_type = 'COUNTRY')
             ship_country,
          ( (SELECT ffp.description
               FROM apps.fnd_flex_values_vl ffp,
                    apps.fnd_flex_value_sets flex_sets1
              WHERE     ffp.flex_value = g.segment4
                    AND flex_sets1.flex_value_set_id = ffp.flex_value_set_id
                    AND flex_sets1.flex_value_set_name = 'PROQ_BUSINESS_UNIT'))
             business_unit,
          ( (SELECT ffp.description
               FROM apps.fnd_flex_values_vl ffp,
                    apps.fnd_flex_value_sets flex_sets1
              WHERE     ffp.flex_value = g.segment5
                    AND flex_sets1.flex_value_set_id = ffp.flex_value_set_id
                    AND flex_sets1.flex_value_set_name =
                           'PROQ_PRODUCT_FAMILY'))
             product_family,
          mtl.segment1 item,
          mtl.description,
          cleb.start_date,
          TO_CHAR (cleb.start_date, 'yyyymm') start_date_formated,
          cleb.end_date,
          TO_CHAR (cleb.end_date, 'yyyymm') end_date_formated,
          cleb.currency_code currency_code,
          ROUND (kln.toplvl_adj_price * kln.toplvl_price_qty, 2) netprice,
          NVL (
             (SELECT conversion_rate
                FROM apps.gl_daily_rates
               WHERE     from_currency = cleb.currency_code
                     AND conversion_date =
                            NVL (TRUNC (chrb.date_approved), TRUNC (SYSDATE))
                     AND to_currency = 'USD'
                     AND conversion_type = '1024'),
             1)
             conversion_rate2usd,
            NVL (
               (SELECT conversion_rate
                  FROM apps.gl_daily_rates
                 WHERE     from_currency = cleb.currency_code
                       AND conversion_date =
                              NVL (TRUNC (chrb.date_approved),
                                   TRUNC (SYSDATE))
                       AND to_currency = 'USD'
                       AND conversion_type = '1024'),
               1)
          * (ROUND (kln.toplvl_adj_price * kln.toplvl_price_qty, 2))
             price_in_usd,
          chrb.attribute8 batch_number,
          (SELECT notice_number
             FROM pqinf.xxpq_renewal_stg
            WHERE batch_id = chrb.attribute8 AND chrb.org_id = org_id)
             renewal_notice_no,
          PQINF.xxpq_get_sc_notes (chrb.id) sc_notes,
          DECODE (
             cleb.sts_code,
             'TERMINATED', (SELECT v.meaning
                              FROM apps.okc_k_lines_b l,
                                   apps.fnd_lookup_values_vl v,
                                   apps.fnd_lookup_types_vl ty
                             WHERE     l.ID = cleb.ID
                                   AND l.trn_code = v.lookup_code
                                   AND v.lookup_type = ty.lookup_type
                                   AND ty.lookup_type IN
                                          ('OKC_TERMINATION_REASON')),
             NULL)
             termination_reason,
          DECODE (
             cleb.sts_code,
             'TERMINATED', NULL,
             'ACKNOWLEGED', (SELECT reason_code
                               FROM apps.okc_k_history_b hist1
                              WHERE     chr_id = chrb.ID
                                    AND hist1.sts_code_to = cleb.sts_code
                                    AND hist1.creation_date =
                                           (SELECT MAX (hist2.creation_date)
                                              FROM apps.okc_k_history_b hist2
                                             WHERE hist2.chr_id =
                                                      hist1.chr_id)
                                    AND ROWNUM = 1),
             (SELECT v.meaning
                FROM apps.okc_k_lines_b l,
                     apps.fnd_lookup_values_vl v,
                     apps.fnd_lookup_types_vl ty
               WHERE     l.ID = cleb.ID
                     AND l.trn_code = v.lookup_code
                     AND v.lookup_type = ty.lookup_type
                     AND ty.lookup_type IN ('OKC_STS_CHG_REASON')
                     AND ROWNUM = 1))
             status_change_reason,
          chrb.creation_date,
          chrb.date_approved,
          DECODE (cleb.sts_code,
                  'ACKNOWLEGED', 'ACKNOWLEDGED',
                  cleb.sts_code)
             sts_code,
          hcsu.LOCATION ship_to_location,
          (SELECT btr.trx_date
             FROM apps.oks_bill_transactions btr,
                  apps.oks_bill_txn_lines btl,
                  apps.oks_bill_cont_lines bcl,
                  apps.okc_k_headers_all_b hdr,
                  apps.okc_k_lines_b line
            WHERE     hdr.ID = chrb.ID
                  AND hdr.ID = line.chr_id
                  AND line.ID = bcl.cle_id
                  AND btr.ID = bcl.btn_id
                  AND btl.btn_id = btr.ID
                  AND btl.bcl_id = bcl.ID
                  AND ROWNUM = 1)
             inv_date,
          (SELECT btr.trx_number
             FROM apps.oks_bill_transactions btr,
                  apps.oks_bill_txn_lines btl,
                  apps.oks_bill_cont_lines bcl,
                  apps.okc_k_headers_all_b hdr,
                  apps.okc_k_lines_b line
            WHERE     hdr.ID = chrb.ID
                  AND hdr.ID = line.chr_id
                  AND line.ID = bcl.cle_id
                  AND line.ID = cleb.ID
                  AND btr.ID = bcl.btn_id
                  AND btl.btn_id = btr.ID
                  AND btl.bcl_id = bcl.ID
                  AND ROWNUM = 1)
             inv_number,
          DECODE (hl.country, 'US', 'US', 'International') us_international
     FROM apps.okc_k_lines_b cleb,
          apps.okc_k_lines_tl clet,
          apps.oks_k_lines_b kln,
          apps.oks_k_lines_tl klt,
          apps.okc_k_items citem,
          apps.okc_k_headers_all_b chrb,
          apps.okc_k_headers_tl chrt,
          apps.oks_k_headers_b khr,
          --   apps.okc_statuses_v sts,
          apps.mtl_system_items_b mtl,
          --     apps.okc_subclasses_v cls,
          apps.hz_locations hl,
          apps.hz_party_sites hps,
          apps.hz_cust_accounts hca,
          apps.hz_cust_acct_sites_all hcas,
          apps.hz_cust_site_uses_all hcsu,
          apps.gl_code_combinations_v g
    WHERE     cleb.ID = clet.ID
          AND clet.LANGUAGE = USERENV ('LANG')
          AND clet.LANGUAGE = klt.LANGUAGE
          AND cleb.ID = kln.cle_id
          AND kln.ID = klt.ID
          AND cleb.chr_id = chrb.ID
          AND citem.object1_id1 = mtl.inventory_item_id
          AND mtl.sales_account = g.code_combination_id
          AND kln.cle_id = citem.cle_id
          AND chrb.ID = chrt.ID
          AND chrb.ID = khr.chr_id
          AND chrt.LANGUAGE = USERENV ('LANG')
          --   AND chrb.sts_code = sts.code
          --   AND chrb.scs_code = cls.code
          --   AND cls.cls_code = 'SERVICE'
          AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
          AND hcas.cust_account_id = hca.cust_account_id
          AND hcas.party_site_id = hps.party_site_id
          AND hps.location_id = hl.location_id
          AND hcsu.site_use_id = cleb.ship_to_site_use_id
          AND cleb.sts_code NOT IN ('ACTIVE',
                                    'AMENDED',
                                    'APPROVAL_REJECTED',
                                    'CANCELLED',
                                    'COMPLETE',
                                    'DECLINED',
                                    'ENTERED',
                                    'EXPIRED',
                                    'HOLD',
                                    'MIGRATE',
                                    'PROFORMA',
                                    'RENEWAL_ACTIVE',
                                    'REVERSED',
                                    'REVIEWED',
                                    'SALES_PRICE_REVIEW_REQ',
                                    'SIGNED',
                                    'ABANDONED',
                                    'TERMINATED')
                                   -- and chrb.contract_number='US10042610'
                                   and hca.attribute2 like 'Personal%'
            AND cleb.start_date BETWEEN '01-FEB-2018' and '15-FEB-2018'
            --AND trunc(cleb.start_date) = trunc(to_date(:p_Start_Date, 'YYYY/MM/DD hh24:mi:ss'))
            --AND trunc(cleb.start_date) = trunc(to_date(:p_end_Date, 'YYYY/MM/DD hh24:mi:ss'))

Is there any way i can handle this but still have the "&" in the end output in xml output


Sample Data:
------------------

CREATE TABLE APPS.TEST_H
(
ORG VARCHAR2(5 BYTE),
MARKET_TYPE VARCHAR2(150 BYTE),
SUB_MARKET_TYPE VARCHAR2(150 BYTE),
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
LINE_NUMBER VARCHAR2(150 BYTE) NOT NULL,
SHIP_ACCOUNT_NUMBER VARCHAR2(30 BYTE) NOT NULL,
BILL_TO_ACCOUNT_NAME VARCHAR2(240 BYTE),
BILL_TO_ACCOUNT_NUMBER VARCHAR2(30 BYTE),
SHIP_ACCOUNT_NAME VARCHAR2(240 BYTE),
CUST_PO_NUMBER_REQ_YN VARCHAR2(3 BYTE),
SITE_DEPT VARCHAR2(240 BYTE),
ADDRESS1 VARCHAR2(240 BYTE) NOT NULL,
ADDRESS2 VARCHAR2(240 BYTE),
CITY VARCHAR2(60 BYTE),
STATE VARCHAR2(60 BYTE),
POSTAL_CODE VARCHAR2(60 BYTE),
COUNTRY VARCHAR2(60 BYTE) NOT NULL,
SHIP_COUNTRY VARCHAR2(360 BYTE),
BUSINESS_UNIT VARCHAR2(240 BYTE),
PRODUCT_FAMILY VARCHAR2(240 BYTE),
ITEM VARCHAR2(40 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
START_DATE DATE,
START_DATE_FORMATED VARCHAR2(6 BYTE),
END_DATE DATE,
END_DATE_FORMATED VARCHAR2(6 BYTE),
CURRENCY_CODE VARCHAR2(15 BYTE),
NETPRICE NUMBER,
CONVERSION_RATE2USD NUMBER,
PRICE_IN_USD NUMBER,
BATCH_NUMBER VARCHAR2(450 BYTE),
RENEWAL_NOTICE_NO VARCHAR2(50 BYTE),
SC_NOTES VARCHAR2(4000 BYTE),
TERMINATION_REASON VARCHAR2(80 BYTE),
STATUS_CHANGE_REASON VARCHAR2(80 BYTE),
CREATION_DATE DATE NOT NULL,
DATE_APPROVED DATE,
STS_CODE VARCHAR2(30 BYTE),
SHIP_TO_LOCATION VARCHAR2(40 BYTE) NOT NULL,
INV_DATE DATE,
INV_NUMBER VARCHAR2(60 BYTE),
US_INTERNATIONAL VARCHAR2(13 BYTE)
);

COMMIT;

Insert into APPS.TEST_H
(ORG, MARKET_TYPE, SUB_MARKET_TYPE, CONTRACT_NUMBER, LINE_NUMBER,
SHIP_ACCOUNT_NUMBER, BILL_TO_ACCOUNT_NAME, BILL_TO_ACCOUNT_NUMBER, SHIP_ACCOUNT_NAME, SITE_DEPT,
ADDRESS1, CITY, POSTAL_CODE, COUNTRY, SHIP_COUNTRY,
BUSINESS_UNIT, PRODUCT_FAMILY, ITEM, DESCRIPTION, START_DATE,
START_DATE_FORMATED, END_DATE, END_DATE_FORMATED, CURRENCY_CODE, NETPRICE,
CONVERSION_RATE2USD, PRICE_IN_USD, SC_NOTES, STATUS_CHANGE_REASON, CREATION_DATE,
STS_CODE, SHIP_TO_LOCATION, US_INTERNATIONAL)
Values
('US', 'Corporate', 'Personal & Household Products    ', 'PQ10077640', '3',
'390680', 'Philips International B.V.', '390680', 'Philips International B.V.', 'Research',
'PB 80071', 'Eindhoven', '5600 KA', 'NL', 'Netherlands',
'DIALOG', 'PROQUEST DIALOG', 'PQDALL', 'ProQuest Dialog Database', TO_DATE('02/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'201802', TO_DATE('01/31/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '201901', 'USD', 10000,
1, 10000, '|60 days End of Month|** Invoices should be sent via regular post to the billing address as
indicated on this Purchase Order **
Invoicing has to be done in full conformity with our Purchase Order.We
urge you strongly to indicate our correct billing address and complete
PO number NLY0- (as indicated on this order form) on your invoice(s).
Incorrect invoices cannot be processed by Philips and will be returned.
For invoice related questions/issues you can contact:
Email: vq.ct.nl@philips.com
Phone: +31 40 27 82271 (Dutch / English language)
+31 40 27 82272 (French language)
Please send your order confirmation within three days to the email
address as mentioned in header of this order.', 'CREATE', TO_DATE('02/16/2018 04:02:31', 'MM/DD/YYYY HH24:MI:SS'),
'ACKNOWLEDGED', '2558937', 'International');
Insert into APPS.TEST_H
(ORG, MARKET_TYPE, SUB_MARKET_TYPE, CONTRACT_NUMBER, LINE_NUMBER,
SHIP_ACCOUNT_NUMBER, BILL_TO_ACCOUNT_NAME, BILL_TO_ACCOUNT_NUMBER, SHIP_ACCOUNT_NAME, SITE_DEPT,
ADDRESS1, CITY, POSTAL_CODE, COUNTRY, SHIP_COUNTRY,
BUSINESS_UNIT, PRODUCT_FAMILY, ITEM, DESCRIPTION, START_DATE,
START_DATE_FORMATED, END_DATE, END_DATE_FORMATED, CURRENCY_CODE, NETPRICE,
CONVERSION_RATE2USD, PRICE_IN_USD, SC_NOTES, STATUS_CHANGE_REASON, CREATION_DATE,
STS_CODE, SHIP_TO_LOCATION, US_INTERNATIONAL)
Values
('US', 'Corporate', 'Personal & Household Products    ', 'PQ10077640', '1',
'390680', 'Philips International B.V.', '390680', 'Philips International B.V.', 'Research',
'PB 80071', 'Eindhoven', '5600 KA', 'NL', 'Netherlands',
'DIALOG', 'DIALOG', 'INVESTEXT', 'Thomson Reuters Embargoed Research Collection (Shell)', TO_DATE('02/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'201802', TO_DATE('01/31/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '201901', 'USD', 2240,
1, 2240, '|60 days End of Month|** Invoices should be sent via regular post to the billing address as
indicated on this Purchase Order **
Invoicing has to be done in full conformity with our Purchase Order.We
urge you strongly to indicate our correct billing address and complete
PO number NLY0- (as indicated on this order form) on your invoice(s).
Incorrect invoices cannot be processed by Philips and will be returned.
For invoice related questions/issues you can contact:
Email: vq.ct.nl@philips.com
Phone: +31 40 27 82271 (Dutch / English language)
+31 40 27 82272 (French language)
Please send your order confirmation within three days to the email
address as mentioned in header of this order.', 'CREATE', TO_DATE('02/16/2018 04:02:31', 'MM/DD/YYYY HH24:MI:SS'),
'ACKNOWLEDGED', '2558937', 'International');
COMMIT;
  • Attachment: Capture1.PNG
    (Size: 79.68KB, Downloaded 465 times)
Previous Topic: Split Data in Column & Split Data in Excel (merged)
Next Topic: Last 7 days report
Goto Forum:
  


Current Time: Fri Nov 15 14:39:05 CST 2019