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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
...
where extractvalue(xmlcol,'/system/deviceName') is not null
...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.