Oracle 12C XML query using a WHERE CLAUSE

Created an XML table and loaded data as follows,

CREATE TABLE xml_table (xml_data XMLType);

INSERT INTO xml_table VALUES (XMLType(bfilename('XML_DIR', '50M_XML_file.xml'),
                                     nls_charset_id('AL32UTF8')));

This query works

select distinct FpaNumber
from xml_table,
    xmltable(
         xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
            'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"
        ),
       '//FixPostingArea[BaselineSectorName="ZHN88"]/FpaNumber'
    passing xml_data
    columns
        FpaNumber           varchar2(4)  path '//FpaNumber'
    ) xml_data
;

but when I try to create this as a cursor where BaselineSectorName=rec_BaselineSectorName from a cursor above, its not seen and ends up as NULL. So I tried to use a WHERE CLAUSE as follows but no FpaNumber was returned. No errors either. Syntax problem?  Also I'm just assuming if I get the WHERE CLAUSE to work then the cursor will function properly but that might not be a valid assumption.

select distinct FpaNumber
from xml_table,
    xmltable(
         xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
            'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"
        ),
       '//FixPostingArea/BaselineSectorName/FpaNumber'
    passing xml_data
    columns
        FpaNumber           varchar2(4)  path '//FpaNumber'
    ) xml_data
  where xml_data.BaselineSectorName = 'ZHN88';

-- no records retuned

select FpaNumber
from xml_table,
      xmltable(
             xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
                  'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"),
    '//FixPostingArea/BaselineSectorName'
      passing xml_data
      columns
            BaselineSectorName number(10) path '//FixPostingArea/BaselineSectorName'
      ) xml_data,
      xmltable(
              xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
                  'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"),
    '//FixPostingArea/BaselineSectorName/FpaNumber'
      passing xml_data
      columns
            FpaNumber number(4) path '//FixPostingArea/BaselineSectorName/FpaNumber'
      ) xml_data
     where xml_data.BaselineSectorName = 'ZHN88';

-- no records retuned
talahiAsked:
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:
I don't think you need two XMLTable calls.

I would go with the example I posted in your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28562437.html#a40443667

If that doesn't get you what you need, please provide a small example of the XML and your expected results.
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
talahiAuthor Commented:
Works great.  Missed that 'Module_xml xmltype path' and xml_data.module_xml.  

Changed servers since I last did this and working on something similar.  Thanks again.
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.