How do I configure an IF statement in this type of XML query?

How would I configure an IF IS NULL statement in this type of XML query?

For example if the devicename field is NOT blank then print, otherwise don't print?


column TransactionName newline

with myXML as (
select xmltype(TEST_FIELD_NM) 
xmlcol from TEST_DB 
where isvalid(TEST_FIELD_NM) = 1)
select
'Device Name: ' || extractvalue(xmlcol,'/system/deviceName'),
'Transaction Name: ' || extractvalue(xmlcol,'/system/transactionName')
where '/system/deviceName' IS NOT NULL
from myXML;

Open in new window

rie_Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
...
where extractvalue(xmlcol,'/system/deviceName') is not null
...
0
 
rie_Author Commented:
I tried this and it's not working.  Do I have it in the place?


column TransactionName newline

with myXML as (
select xmltype(TEST_FIELD_NM) 
xmlcol from TEST_DB 
where isvalid(TEST_FIELD_NM) = 1)
select
'Device Name: ' || extractvalue(xmlcol,'/system/deviceName'),
'Transaction Name: ' || extractvalue(xmlcol,'/system/transactionName')
where extractvalue(xmlcol,'/system/deviceName') is not null
from myXML;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Define 'not working'.  Are you getting a syntax error or are you not getting the desired results.

If the latter: what results are you getting and what do you want?

I can also read the question to mean that you do not want to see 'Device Name: ' if there is nothing to display.  If this is the question, it is not in the where clause:

...
case when extractvalue(xmlcol,'/system/deviceName') is not null then 'Device Name: ' end || extractvalue(xmlcol,'/system/deviceName'),
...

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rie_Author Commented:
Here's the error I am getting:

where extractvalue(xmlcol,'/system/deviceName') is not null
*
ERROR at line 16:
ORA-00923: FROM keyword not found where expected

If devicename is empty then the xml is not there so it is printing out the following:

Device Name:  
Transaction Name:

with a blank in the column.  

I would like to not see anything if these columns are blank.

I have over 7,000 records that do not contain anything at all in this field and would only like to see the 3,000 that contain data in these columns only.
0
 
slightwv (䄆 Netminder) Commented:
>>ORA-00923: FROM keyword not found where expected

I cannot help unless I see the complete SQL.

>>I would like to not see anything if these columns are blank.

re: http:#a37390118

...
case when extractvalue(xmlcol,'/system/deviceName') is not null then 'Device Name: ' end || extractvalue(xmlcol,'/system/deviceName'),
...
0
 
rie_Author Commented:
Ok - Here you go.


set pages 0
set feedback off
column TransactionName newline

with myXML as (
select xmltype(TEST_FIELD_NM) 
xmlcol from TEST_DB 
where isvalid(TEST_FIELD_NM) = 1)
select
'Device Name: ' || extractvalue(xmlcol,'/system/deviceName'),
'Meter Number VAL1: ' || extractvalue(xmlcol,'/system/meterNumber/VAL1'),
'Meter Number VAL2: ' || extractvalue(xmlcol,'/system/meterNumber/VAL2'),
'Revenue Upload Indicator Code: ' || extractvalue(xmlcol,'/system/revenueUploadIndicatorCode'),
'Source System Name: ' || extractvalue(xmlcol,'/system/sourceSystemName'),
'System Server Name VAL1: ' || extractvalue(xmlcol,'/system/sourceSystemServerName/VAL1'),
'System Server Name VAL2: ' || extractvalue(xmlcol,'/system/sourceSystemServerName/VAL2'),
'Software Version VAL1: ' || extractvalue(xmlcol,'/system/softwareVersion/VAL1'),
'Software Version VAL2: '|| extractvalue(xmlcol,'/system/softwareVersion/VAL2'),
'Transaction Name: ' || extractvalue(xmlcol,'/system/transactionName')
where extractvalue(xmlcol,'/system/deviceName') is not null
from myXML;

Open in new window

0
 
rie_Author Commented:
Would like to see the following only when I have data:

Device Name: COFFEE
Meter Number VAL1: 4253624
Meter Number VAL2: 4253625
Revenue Upload Indicator Code: Y
Source System Name: SAM
System Server Name VAL1: BLDKI01
System Server Name VAL2: BLDKI02
Software Version VAL1: COFFEE09
Software Version VAL2: COFFEE10
Transaction Name: STO534

I do not want see this if I do not have any data.  It's always all blanks when no data or all filled as above when I have data.

Device Name:
Meter Number VAL1:
Meter Number VAL2:
Revenue Upload Indicator Code:
Source System Name:
System Server Name VAL1:
System Server Name VAL2:
Software Version VAL1:
Software Version VAL2:
Transaction Name:
0
 
slightwv (䄆 Netminder) Commented:
Proper select syntax is:
Select stuff
From tablename
Where some clause

Move your where after the from.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.