KFGC KFGC - 3 months ago 10
SQL Question

Error generating KML files with SQL

I want to generate kml files using sql to locate some points in google earth. I have this code:

with xmlnamespaces(default 'http://www.opengis.net/kml/2.2')
select(
select PERIODO_ALTA as name,
1 as visibility,(
select(
select PAGO_ANT_60DF as name,
mir_mdf_troba as description,
MIR_COORD_X_LON+','+MIR_COORD_Y_LAT+',0' as 'Point/coordinates'
)for xml path('Placemark'), type
)from CB_IC_FIJA_AN_CONSOLIDADO_RECIBOS_3X
WHERE DEPARTAMENTO ='LA LIBERTAD'
AND MIR_COORD_Y_LAT IS NOT NULL AND MIR_COORD_Y_LAT<>'0'
for xml path('Folder'), type
)for xml path('Document'), root('kml');


and get this error message:
Msg 116, Level 16, State 1, Line 9
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Answer

Solved, thanks!

with xmlnamespaces(default 'http://www.opengis.net/kml/2.2')
select (
       select Distrito+MDF_TROBA_NOMBRE as name,
              'MIR' as description,
              (
                select 0 as extrude,
                       'relativeToGround' as altitudeMode,
                       '-76.90695,-11.99474 -76.90292,-11.99533 -76.90086,-12.01296 
                       -76.90343,-12.01388 -76.90627,-12.01019 -76.90695,-11.99474' 
                       as 'outerBoundaryIs/LinearRing/coordinates'
                for xml path('Polygon'), type
              )
       from MIR_FIJA_MAESTRA_CAB
       for xml path('Placemark'), type
       )
for xml path('Document'), root('kml');