Solved

Syntax xmltable query oracle multiple variables

Posted on 2014-11-14
9
500 Views
Last Modified: 2014-11-20
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>
0
Comment
Question by:talahi
  • 5
  • 4
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40443667
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
 

Author Comment

by:talahi
ID: 40447416
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40447669
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
 

Author Comment

by:talahi
ID: 40448140
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40448181
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
 

Author Comment

by:talahi
ID: 40455782
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40455805
>>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
 

Author Comment

by:talahi
ID: 40455849
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
 

Author Closing Comment

by:talahi
ID: 40455852
Thanks again.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now