My Oracle Support Banner

Unpivot operation on view with xmlTable is returning incorrect results (Doc ID 2223258.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms

A view containing the XMLTable function is created to display an XMLTYPE column's contents in row-column format. When the UNPIVOT operation is applied to five of the view's columns, only the first is displayed after the operation, see below:

SQL> SELECT t.transaction_id,
  2         t.billing_rule_data_xml.getClobVal() AS billing_rule_data_xml
  3  FROM test_xml_transaction t;

TRANSACTION_ID BILLING_RULE_DATA_XML
--------------- --------------------------------------------------------------
         116895 <BillingRuleData>
                  <CarsalesEnquiryData>
                    <ContactName>kim</ContactName>
                    <EmailAddress>kim@yahoo.com</EmailAddress>
                    <HomePhone>0212449614</HomePhone>
                    <WorkPhone>0212449614</WorkPhone>
                    <MobilePhone>0487612345</MobilePhone>
                  </CarsalesEnquiryData>
                </BillingRuleData>

1 row selected.

SQL> CREATE OR REPLACE
  2  VIEW my_test_xml_view
  3  AS
  4  SELECT transaction_id,
  5         contact_name,
  6         email_address,
  7         home_phone,
  8         work_phone,
  9         mobile_phone
 10  FROM test_xml_transaction,
 11       XMLtable
 12                (
 13                  '$i/BillingRuleData/CarsalesEnquiryData'
 14                  PASSING billing_rule_data_xml AS "i"
 15                  COLUMNS
 16                   contact_name  VARCHAR2(60)  PATH 'ContactName',
 17                   email_address VARCHAR2(100) PATH 'EmailAddress',
 18                   home_phone    VARCHAR2(14)  PATH 'HomePhone',
 19                   work_phone    VARCHAR2(14)  PATH 'WorkPhone',
 20                   mobile_phone  VARCHAR2(14)  PATH 'MobilePhone'
 21  );

View created.

SQL> desc my_test_xml_view
Name                       Null?    Type
-------------------------- -------- ----------------
TRANSACTION_ID             NOT NULL NUMBER(20)
CONTACT_NAME                        VARCHAR2(60)
EMAIL_ADDRESS                       VARCHAR2(100)
HOME_PHONE                          VARCHAR2(14)
WORK_PHONE                          VARCHAR2(14)
MOBILE_PHONE                        VARCHAR2(14)

SQL> SELECT transaction_id,
  2         contact_name,
  3         mobile_phone,
  4         home_phone,
  5         work_phone,
  6         email_address
  7  FROM my_test_xml_view;

TRANSACTION_ID CONTACT_NAME MOBILE_PHONE HOME_PHONE WORK_PHONE EMAIL_ADDRESS
-------------- ------------ ------------ ---------- ---------- -------------
        116895 kim          0487612345   0212449614 0212449614 kim@yahoo.com

1 row selected.

SQL> SELECT transaction_id,
  2         test_result,
  3         sql_column
  4  FROM ( SELECT transaction_id,
  5                contact_name,
  6                mobile_phone,
  7                home_phone,
  8                work_phone,
  9                email_address
 10         FROM my_test_xml_view
 11       )
 12  UNPIVOT ( ( sql_column)
 13              FOR test_result IN ( contact_name,
 14                                   mobile_phone,
 15                                   home_phone,
 16                                   work_phone,
 17                                   email_address
 18                                  )
 19          );

TRANSACTION_ID TEST_RESULT SQL_COLUMN
-------------- ------------- ------------------------------
116895 CONTACT_NAME kim

1 row selected.

The correct and expected output is:

  TRANSACTION_ID TEST_RESULT   SQL_COLUMN
---------------- ------------- ------------------------------
          116895 CONTACT_NAME  kim
          116895 MOBILE_PHONE  0487612345
          116895 HOME_PHONE    0212449614
          116895 WORK_PHONE    0212449614
          116895 EMAIL_ADDRESS kim@yahoo.com

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.