CODEWITHSUNDEEP
sqloracle-databasexmltypexmltable
user8592197
user8592197

Reputation: 77

XMLTABLE in oracle how to handle null columns'

Hi, I need to extract data from one of the tables which have one XML column. For some of the records, the XML column is not having any data. And when I am using the below query I am getting error.

We need to run this query, whether the table has data or not in the XML column.

CREATE TABLE XMLTAB_1 (A_ID NUMBER NOT NULL
                   , ID VARCHAR2(50) 
                   , A_DATE DATE 
                   , O_ID NUMBER 
                   , XML_KEY CLOB);


INSERT INTO  XMLTAB_1 (A_ID, ID, A_DATE ,O_ID)
VALUES (11,'SA-AND-11-12345',sysdate,null);
INSERT INTO  XMLTAB_1 (A_ID, ID, A_DATE ,O_ID,XML_KEY )
VALUES (12,'SA-AND-12-12345',sysdate,null,'<?xml version ="1.0" encoding ="UTF-16" standalone="n'"?><al><al_cd><comments><columns><time>2020-07-07 08:45:08 </time><auth>xzy</auth><a_status>new</a_status><assgn>katy</assgn><text>new record </text></columns><columns><time>2020-07-07 09:45:08 </time><auth>abcd</auth><a_status>in progress</a_status><assgn>katy_1</assgn><text>work in progress </text></columns></comments></al_cd></al>');
COMMIT; 

SELECT A_ID
       , ID
       , A_DATE
       , O_ID
       , TO_DATE(C_time,'YYYY_MM_DD HH24:MI:SS') ctime
       , auth
       , status
       , assgn 
       , cmmnt  
       , RANK() OVER (PARTITION BY A_ID ORDER BY TO_DATE(C_time,'YYYY_MM_DD HH24:MI:SS') DESC) rnk
FROM XMLTAB_1 a
     , XMLTABLE('al/al_cd/comments/columns'
           PASSING XMLTYPE(a.XML_KEY) COLUMNS
                c_time   VARCHAR2(100) PATH 'time'
                , auth   VARCHAR2(100) PATH 'auth'
                , status VARCHAR2(100) PATH 'a_status'
                , assgn  VARCHAR2(100) PATH 'assgn'
                , cmmnt  VARCHAR2(100) PATH 'text') xt

I am getting error for record a_id as 11 fo 12 it's working fine.

ORA-06502: PL/SQL: Numeric or value error ORA:-06512 :at "SYS.XMLTYPE", line 272

Could anyone please let me know how to achieve this and remove the error from the code.

Upvotes: 0

Views: 2997

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

Assuming you don't just want to filter out the rows with null values, completely, in a where clause, you could use a case expression within the passing clause:

...
XMLTABLE('al/al_cd/comments/columns'
   PASSING CASE WHEN a.XML_KEY IS NULL THEN NULL ELSE XMLTYPE(a.XML_KEY) END
   COLUMNS c_time VARCHAR2(100) PATH 'time',
...

The XPath looks odd, and possibly should be '/al/al_cd/comments/columns', though it works with either.

db<>fiddle building on @vBoka's. I've changed the old-style cross join to an outer apply to make the row with the null value show up, which requires Oracle 12c+.

If you're on an earlier version when you can use an outer join with an always-true condition, which is a bit of a hack. db<>fiddle; or with your sample data, which gives:

A_ID | ID              | A_DATE    | O_ID | CTIME     | AUTH | STATUS      | ASSGN  | CMMNT             | RNK
---: | :-------------- | :-------- | ---: | :-------- | :--- | :---------- | :----- | :---------------- | --:
  11 | SA-AND-11-12345 | 15-JUL-20 | null | null      | null | null        | null   | null              |   1
  12 | SA-AND-12-12345 | 15-JUL-20 | null | 07-JUL-20 | abcd | in progress | katy_1 | work in progress  |   1
  12 | SA-AND-12-12345 | 15-JUL-20 | null | 07-JUL-20 | xzy  | new         | katy   | new record        |   2

Upvotes: 3

Related Questions