1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| SELECT
XMLELEMENT("NavigationPointList",
(SELECT XMLAgg(
XMLELEMENT("NavigationPoint",
XMLELEMENT("PointName", geo.POINT_NAME ),
XMLELEMENT("PointKind", DECODE(geo.NAVAID_TYPE,'DESIGNATED_POINT','DESIGNATED',DECODE(geo.NAVAID_TYPE,'UNKNOWN_POINT','UNKNOWN','NAVAID_'||geo.RADIO_NAVAID_KIND))),
XMLELEMENT("Position",
XMLELEMENT("Latitude",ROUND((geo.DEG_LATITUDE + geo.MIN_LATITUDE/60.0 + geo.SEC_LATITUDE/3600.0),10)),
XMLELEMENT("Hemisphere",DECODE(geo.HEMISPHERE,'N','NORTH','SOUTH','')),
XMLELEMENT("Longitude",ROUND((geo.DEG_LONGITUDE + geo.MIN_LONGITUDE/60.0 + geo.SEC_LONGITUDE/3600.0),10)),
XMLELEMENT("Direction",DECODE(geo.DIRECTION,'W','WEST','EAST',''))
),
XMLELEMENT("MapsUsageAtcCentreList",
(SELECT XMLAgg(
XMLELEMENT("MapsUsageAtcCentre",
XMLELEMENT("AtcCentreName", ATC_CENTRE_NAME ),
XMLELEMENT("MapsUsage", MAPS_USAGE_DESCRIPTOR )
)
)
FROM MAPS_USAGE_TMP n
WHERE n.NAME=gp.NAME and n.KIND=gp.KIND
)
)
)
)
FROM GEO_PT geo,GRAPHICAL_POINT gp
WHERE gp.AIRSPACE_ENV_NAME=geo.AIRSPACE_ENV_NAME
and gp.POINT_NAME#GEO_PT=geo.POINT_NAME
and geo.AIRSPACE_ENV_NAME = 'MAASNEW'
)) from dual; |
Partager