Leggere un file XML con SQL Server 2005

L'esempio seguente mostra come leggere un file XML (d:\file.kml), nello specifico un fille KML di google, ed estrarre alcune informazioni (nome e coordinate). Il tutto realizzato con il T-SQL di Microsoft SQL Server 2005.

SQL

WITH XMLNAMESPACES (DEFAULT 'http://earth.google.com/kml/2.1' )
SELECT x.query('kml/Document/name').value('.', 'varchar(150)') AS [name]
  , x.query('kml/Document/Placemark/Polygon/outerBoundaryIs/LinearRing/coordinates').value('.', 'varchar(1000)') as [coordinates]
  , getdate() as [importDate]
FROM (
  SELECT CAST(x as XML)
  FROM OPENROWSET ( BULK 'd:\file.kml', SINGLE_BLOB  ) AS T(x)
) T(x)
Una parte del file KML di esempio è 
<kml>
  <Document>
    <name>20090409_mio_file</name>
    <Placemark>
      <name>Image 4 vertices</name>
      <styleUrl>#msn_ylw-pushpin</styleUrl>
      <Polygon>
        <tessellate>1</tessellate>
        <outerBoundaryIs>
          <LinearRing>
            <coordinates>17.028466,38.910937,36.825455 17.394437,38.963491,32.837000 17.275401,39.465114,35.121208 16.910579,39.413042,234.794510 17.028466,38.910937,36.825455</coordinates>
          </LinearRing>
        </outerBoundaryIs>
      </Polygon>
    </Placemark>
  </Document>
</kml>
eseguendo la query si ottiene come risultato:

Text

name=20090409_mio_file
coordinates=17.028466,38.910937,36.825455 17.394437,38.963491,32.837000 17.275401,39.465114,35.121208 16.910579,39.413042,234.794510 17.028466,38.910937,36.825455
importDate=... la data corrente ...
Tags:
Database75 SQL90 SQL Server100 T-SQL66 XML / XSL / XSLT29
Potrebbe interessarti anche: