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?
 
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
 
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
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.