Syntax xmltable query oracle multiple variables

Thanks to Expert "slightwv" I was able to get this far.  I was successful in using the XMLTABLE function to query an XML document with one KNOWN value (where clause).  Now I want to expand the same query to include MULTIPLE known values to get to a specific data value.  The first query below works for one KNOWN value and the 2nd one below is my attempt to add another KNOWN value.  All the data is within the same XML PATH.  This is mostly a Syntax question but I've tried as many variations as I can think of.

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

XML data format outline as follows,

<FixPostingArea><BaselineSectorName>ZHN88</BaselineSectorName>
<FpaNumber>8804</FpaNumber>
...
<Module>
...
<BoundaryPt>
     <SeqNum>0</SeqNum>
</BoundaryPt>
</Module>
</FixPostingArea>
talahiAsked:
Who is Participating?
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:
As I mentioned in the other question:
Use a where clause.

I don't think adding XPATH restrictions like "BaselineSectorName[FpaNumber="8801"]" is what you really want here.

If you want the fpaNumber that is '8804', extract adll the fpaNumber's relationally and add a where clause.

The code is below.

If you want something else, please provide the XML and expected results from the provided XML.

with xml_table as (
select xmltype('
 <FixPostingArea>
 <BaselineSectorName>ZHN88</BaselineSectorName>
 <FpaNumber>8804</FpaNumber>
 <Module>
 <BoundaryPt>
      <SeqNum>0</SeqNum>
 </BoundaryPt>
 </Module>
 </FixPostingArea> '
) xml_data 
from dual
)
select FpaNumber, BaselineSectorName 
 from xml_table,
       xmltable(
     '/FixPostingArea'
       passing xml_data
       columns
            FpaNumber varchar2(4) path '/FixPostingArea/FpaNumber',
 			BaselineSectorName varchar2(10) path '/FixPostingArea/BaselineSectorName'
       ) xml_data 
where fpaNumber = '8804'
/

Open in new window

0
talahiAuthor Commented:
Using the logic you provided above, if I knew the fpaNumber = '8801' from a previous query and I was given BaselineSectorName = 'ZHN88' to search on, why wouldn't this query produce a list of SeqNums (one to many)?  Attached is the XML Data Structure.  This <FixPostingArea> tag is critical because the same data is list ed above under another tag <FpamReference>.  Thanks again.  Any reference would be appreciated for this xmltable function in this type of query.

with xml_table as (
select xmltype('
 <FixPostingArea>
 <BaselineSectorName>ZHN88</BaselineSectorName>
 <FpaNumber>8804</FpaNumber>
 <Module>
 <BoundaryPt>
      <SeqNum></SeqNum>
 </BoundaryPt>
 </Module>
 </FixPostingArea>'
) xml_data
from dual
)
select seqnum, FpaNumber, BaselineSectorName
 from xml_table,
       xmltable(
     '/FixPostingArea'
       passing xml_data
       columns
                        BaselineSectorName varchar2(10) path '/FixPostingArea/BaselineSectorName',
                        FpaNumber varchar2(4) path '/FixPostingArea/FpaNumber',
                        Seqnum varchar2(10) path '/FixPostingArea/Module/BoundaryPt/SeqNum'
       ) xml_data
where fpaNumber = '8804' and BaselineSectorName = 'ZHN88'
/
XML-Data-Structure.txt
0
slightwv (䄆 Netminder) Commented:
Please provide valid XML fragments not just "..." that create broken tags.  This way I can copy/paste the XML you provide and work on the SQL instead of taking time to fix the XML before the SQL will run.

That way I also don't 'guess' wrong on where the closing tags should be.


To get the SeqNums, you need to add another XML_TABLE query.

Note:  This generates an error on 11.2.0.2 that appears to be a bug.  It runs in 12.1.0.2.

Hopefully you have something above 11.2.0.2.

with xml_table as (
select xmltype('
<TfmsData xmlns="http://tfm.aaa.xxx/tfms/TfmsData" xmlns:nxce="http://tfm.aaa.xxx/tfms/NasXCoreElements">
<GeospatialData>
<FixPostingArea>
<BaselineSectorName>ZHN88</BaselineSectorName>
<FpaNumber>8801</FpaNumber>
<Min><nxce:latitude><nxce:latitudeRadians>.1637264277</nxce:latitudeRadians></nxce:latitude><nxce:longitude><nxce:longitudeRadians>2.4551595074</nxce:longitudeRadians></nxce:longitude></Min>
<Max><nxce:latitude><nxce:latitudeRadians>.3086905676</nxce:latitudeRadians></nxce:latitude><nxce:longitude><nxce:longitudeRadians>2.6032894792</nxce:longitudeRadians></nxce:longitude></Max>
<MinAlt>0</MinAlt>
<MaxAlt>99900</MaxAlt>
<Module>
 <ModuleID>01</ModuleID>
 <Min><nxce:latitude><nxce:latitudeRadians>.1637264277</nxce:latitudeRadians></nxce:latitude><nxce:longitude><nxce:longitudeRadians>2.4551595074</nxce:longitudeRadians></nxce:longitude></Min>
 <Max><nxce:latitude><nxce:latitudeRadians>.3086905676</nxce:latitudeRadians></nxce:latitude><nxce:longitude><nxce:longitudeRadians>2.6032894792</nxce:longitudeRadians></nxce:longitude></Max>
 <MinAlt>0</MinAlt>
 <MaxAlt>17000</MaxAlt>
  <BoundaryPt>
    <SeqNum>0</SeqNum>
    <GeoPosition><nxce:latitude><nxce:latitudeRadians>.3086905676</nxce:latitudeRadians></nxce:latitude><nxce:longitude><nxce:longitudeRadians>2.5278815584</nxce:longitudeRadians></nxce:longitude></GeoPosition>
  </BoundaryPt>
  <BoundaryPt>
     <SeqNum>1</SeqNum>
     <GeoPosition><nxce:latitude><nxce:latitudeRadians>.3084142227</nxce:latitudeRadians></nxce:latitude><nxce:longitude><nxce:longitudeRadians>2.5358276551</nxce:longitudeRadians></nxce:longitude></GeoPosition>
  </BoundaryPt>
  <BoundaryPt> 
      <SeqNum>2</SeqNum>
  </BoundaryPt>
</Module>
</FixPostingArea>
 </GeospatialData>
</TfmsData>'
) xml_data 
from dual
)
select FpaNumber, BaselineSectorName, SeqNum
 from xml_table,
       xmltable(
              xmlnamespaces(default 'http://tfm.aaa.xxx/tfms/TfmsData',
                   'http://tfm.aaa.xxx/tfms/NasXCoreElements' as "nxce"
             ),
     '/TfmsData/GeospatialData/FixPostingArea'
       passing xml_data
       columns
            FpaNumber varchar2(4) path '/FixPostingArea/FpaNumber',
 			BaselineSectorName varchar2(10) path '/FixPostingArea/BaselineSectorName',
 			Module_xml xmltype path  '/FixPostingArea/Module'
       ) xml_data
       ,xmltable(
              xmlnamespaces(default 'http://tfm.aaa.xxx/tfms/TfmsData',
                   'http://tfm.aaa.xxx/tfms/NasXCoreElements' as "nxce"
             ),
     '/Module/BoundaryPt'
       passing xml_data.module_xml
       columns
            SeqNum varchar2(10) path '/BoundaryPt/SeqNum'
       ) xml_module 
/

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

talahiAuthor Commented:
Database is 12.1.0.2, 64bit, Windows 2012

The ... from the previous file <other data tags> not part of the data-of-interest path but real data attached for 2 SeqNums 0 and 1.

That query does display the following data given: <BaselineSectorName>ZHN88</BaselineSectorName>
<FpaNumber>8801 <SeqNum>1</SeqNum>

8801      ZHN88      0
8801      ZHN88      1
8801      ZHN88      2

path to BaselineSectorName and FpaNumber

<TfmsData>
<GeospatialData>
 <FixPostingArea>
  <BaselineSectorName>
  <FpaNumber>
 

path to SeqNum

<TfmsData>
<GeospatialData><FixPostingArea><Module><BoundaryPt>
       <SeqNum>
</BoundaryPt></Module></FixPostingArea></GeospatialData>

</TfmsData>
test-xml-data.txt
0
slightwv (䄆 Netminder) Commented:
Using the latest XML you posted it generates:
8801 ZHN88      0
8801 ZHN88      1

Open in new window


I didn't change the select at all with the exception of changing the namespaces to the ones you have in the XML.

What are you expecting?
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:
I got tasked with something else which gave me a chance to look at this freshly again today.  Thanks I was able to take it a step further here with the same data.

select BaselineSectorName, FpaNumber, SeqNum, GeoPosition
 from xml_table,
       xmltable(
              xmlnamespaces(default 'http://tfm.aaa.xxx/tfms/TfmsData',
                   'http://tfm.aaa.xxx/tfms/NasXCoreElements' as "nxce"
             ),
     '/TfmsData/GeospatialData/FixPostingArea'
       passing xml_data
       columns
            BaselineSectorName varchar2(10) path '/FixPostingArea/BaselineSectorName',
            FpaNumber varchar2(4) path '/FixPostingArea/FpaNumber',
            Module_xml xmltype path  '/FixPostingArea/Module'
       ) xml_data
       ,xmltable(
              xmlnamespaces(default 'http://tfm.aaa.xxx/tfms/TfmsData',
                   'http://tfm.aaa.xxx/tfms/NasXCoreElements' as "nxce"
             ),
     '/Module/BoundaryPt'
       passing xml_data.module_xml
       columns
           SeqNum varchar2(10) path '/BoundaryPt/SeqNum',
           GeoPosition varchar2(100) path '//GeoPosition'
       ) xml_module
       where BaselineSectorName = 'ZHN88' and FpaNumber = '8801' and seqnum = '0'
/
ZHN88      8801       0     .11141422272.5358276551

One last question if you don't mind.  With the limited data sample this works perfectly.  The real data in a xml_table has the following form.  I assume the path to <FixPostingArea> in the above query should have double // in front instead of a single / . I tried various paths with the only success being with the different type of query at the bottom.  Wondered if there were any other adjustments that would have to be made? I attached the query with data that works but not when queried from the database table xml_table.

Real database table format for xml_table,

<?xml version="1.0" encoding="UTF-8"?>
<TfmsData xmlns="http://tfm.faa.gov/tfms/TfmsData" xmlns:nxce="http://tfm.faa.gov/tfms/NasXCoreElements">
 <GeospatialData>
<Navaid>...</Navaid>
<FixPostingArea>...</FixPostingArea>
<Sector>...</Sector>
<SpecialUseAirspace>...</SpecialUseAirspace>
</GeospatialData>
</TfmsData>

This query works with the table data from xml_table,

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

.21255201352.5990570557

Everything attached.
0
slightwv (䄆 Netminder) Commented:
>>I assume the path to <FixPostingArea> in the above query should have double // in front instead of a single /

If you use two slashes like // that returns ANY and ALL nodes it finds.  I doubt that is what you want or need.  You will likely get 'bad' results or values you do not want.

>>Everything attached.

This is because there are probably more than one GeoPosition node so it returned them 'all'.



You need to add the path to the node you want.

So if you want FixPostingArea, given the data you posted you need /TfmsData/FixPostingArea

>>FixPostingArea[BaselineSectorName="ZHN88"]

I still don't see why you insist on using XPATH to look for your data but you know what you need.  I probably wouldn't do it that way.

>>Wondered if there were any other adjustments that would have to be made?

It all depends on the XML in the table.  If a row doesn't work, you'll need to look at the XML itself and see where the problem is.
0
talahiAuthor Commented:
No I prefer the Where clause its just that the XPATH worked when I was trying different methods to get data out of the table.  At this point it also gives me peace of mind that the data can be extracted with a query.  I'll still continue to work on this WHERE clause playing with different paths to get an output from the table.  Thanks again.
0
talahiAuthor Commented:
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.

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.