asked on

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>

slightwv (䄆 Netminder)

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

talahi

ASKER

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

slightwv (䄆 Netminder)

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

talahi

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

talahi

ASKER

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.

slightwv (䄆 Netminder)

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

talahi

ASKER

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.

talahi

ASKER

Thanks again.