ASA 12 Import files

[eric_verhorstert]

An external program is dumping .xml files in a folder.

As soon as the file is dumped we should import it in our SQL anywhere 12 database and move this file to a other folder.

We do not know the names of the external files but the extension is allways .xml.

Is there a way to import the files using SQL Anywhere 12?

Thanks

Eric

[former_member188493]

You can use the Directory Access Server feature to determine what the file names are.

You can use the LOAD TABLE FORMAT XML clause to use some of the power of the OPENXML operator to load an XML file into relational data...

...it will be easy!

[eric_verhorstert]

Thanks Breck,

Still some problems. I tried

CREATE VARIABLE x XML;

SELECT xp_read_file( 'c:\\klad\\22192MOT4772.xml' )  INTO x;

SELECT * FROM openxml( x, '//*' )

WITH ( attributename CHAR(128) '@name',

       text LONG VARCHAR 'text()' );

This returns the info in multiple rows all as text. I would like to receive each column in the format specified (numeric, char) as 1 row. Is that possible? "Referencia" is the unique key of the row.

The file looks like:

<XML>

  <table name="SCAAnalisisMovilidad" >

  <field name="ConcentracionAnalisis" type="VT_R4" >23.23806</field>

  <field name="IdMuestra" type="VT_R8" >5570.</field>

  <field name="Referencia" type="VT_BSTR" >22192</field>

  <field name="Dilucion" type="VT_R4" >0.</field>

  <field name="Temperatura" type="VT_R4" >37.</field>

  <field name="ImagenesAnalizadas" type="VT_I2" >25</field>

  <field name="ImagenesPorSegundo" type="VT_R4" >25.</field>

  <field name="IdOptica" type="VT_I2" >0</field>

  <field name="IdEscala" type="VT_BSTR" >10x</field>

  <field name="Camara" type="VT_I2" >1</field>

  <field name="AlturaCamara" type="VT_I2" >20</field>

  <field name="AreaMin" type="VT_I4" >10</field>

  <field name="AreaMax" type="VT_I4" >80</field>

  <field name="ValorEL" type="VT_I2" >10</field>

  <field name="ValorLM" type="VT_I2" >25</field>

  <field name="ValorMR" type="VT_I2" >45</field>

  <field name="ValorSTR" type="VT_I2" >45</field>

  <field name="Conectividad" type="VT_I2" >11</field>

  <field name="MinALH" type="VT_I2" >10</field>

  <field name="ValorCirculares" type="VT_I2" >50</field>

  <field name="IdRestriccionesDatos" type="VT_I2" >1</field>

  <field name="IdRestriccionesInformes" type="VT_I2" >1</field>

  <field name="TipoA" type="VT_I2" >61</field>

  <field name="Rapidos" type="VT_I2" >64</field>

  <field name="TipoB" type="VT_I2" >23</field>

  <field name="Medios" type="VT_I2" >26</field>

  <field name="TipoC" type="VT_I2" >30</field>

  <field name="TipoD" type="VT_I2" >8</field>

  <field name="VCL" type="VT_R4" >50.35626221</field>

  <field name="VAP" type="VT_R4" >30.0334568</field>

  <field name="VSL" type="VT_R4" >21.45090866</field>

  <field name="LIN" type="VT_R4" >42.5982933</field>

  <field name="WOB" type="VT_R4" >59.6419487</field>

  <field name="STR" type="VT_R4" >71.42337799</field>

  <field name="ALH" type="VT_R4" >2.56732583</field>

  <field name="BCF" type="VT_R4" >9.056100845</field>

  <field name="CelulasRedondas" type="VT_R4" >0.</field>

  <field name="NumCampos" type="VT_I2" >1</field>

  <field name="VCLoVAP" type="VT_I2" >0</field>

  <field name="Fecha" type="VT_DATE" >2015-03-18 08:49:45</field>

  <field name="Postratado" type="VT_I2" >0</field>

  <field name="VolumenPostratado" type="VT_R4" >2.5</field>

  <field name="Lentos" type="VT_I2" >24</field>

  <field name="Progresivos" type="VT_I2" >81</field>

  <field name="Moviles" type="VT_I2" >114</field>

  <field name="TipoAymas" type="VT_I2" >4</field>

  <field name="Hiperactivados" type="VT_I2" >0</field>

  <field name="SORT1" type="VT_I2" >99</field>

  <field name="SORT2" type="VT_I2" >76</field>

  <field name="SORT3" type="VT_I2" >0</field>

  <field name="TxtSort" type="VT_BSTR" >Hyperactive</field>

  <field name="Area" type="VT_R4" >40.6108284</field>

  <field name="VCLTipoA" type="VT_R4" >68.40280151</field>

  <field name="VAPTipoA" type="VT_R4" >41.19799042</field>

  <field name="VSLTipoA" type="VT_R4" >29.92114449</field>

  <field name="LINTipoA" type="VT_R4" >43.74257278</field>

  <field name="WOBTipoA" type="VT_R4" >60.22851181</field>

  <field name="STRTipoA" type="VT_R4" >72.62767792</field>

  <field name="VCLTipoB" type="VT_R4" >36.33420181</field>

  <field name="VAPTipoB" type="VT_R4" >22.08790779</field>

  <field name="VSLTipoB" type="VT_R4" >15.51653767</field>

  <field name="LINTipoB" type="VT_R4" >42.70504761</field>

  <field name="WOBTipoB" type="VT_R4" >60.79095459</field>

  <field name="STRTipoB" type="VT_R4" >70.24901581</field>

  <field name="VCLTipoC" type="VT_R4" >17.42273712</field>

  <field name="VAPTipoC" type="VT_R4" >8.869052887</field>

  <field name="VSLTipoC" type="VT_R4" >5.292515278</field>

  <field name="LINTipoC" type="VT_R4" >30.37705994</field>

  <field name="WOBTipoC" type="VT_R4" >50.90504837</field>

  <field name="STRTipoC" type="VT_R4" >59.67396164</field>

  <field name="ALHTipoA" type="VT_R4" >2.826558352</field>

  <field name="ALHTipoB" type="VT_R4" >1.776666522</field>

  <field name="BCFTipoA" type="VT_R4" >9.887531281</field>

  <field name="BCFTipoB" type="VT_R4" >6.520237923</field>

  <field name="MAD" type="VT_I2" >0</field>

  <field name="Area" type="VT_R4" >40.6108284</field>

  <field name="AreaTipoA" type="VT_R4" >41.54117584</field>

  <field name="AreaTipoB" type="VT_R4" >40.57239532</field>

  <field name="AreaTipoC" type="VT_R4" >39.84141541</field>

  <field name="AreaTipoD" type="VT_R4" >35.60118103</field>

  <field name="Elongacion" type="VT_I2" >0</field>

  <field name="TrayectoriaCircular" type="VT_I2" >77</field>

  <field name="Droplets" type="VT_I2" >-1</field>

  <field name="Imagen" type="VT_UNKNOWN" />

  <field name="IdEspecie" type="VT_I2" >1</field>

  </table>

</XML>

Thanks

Eric

[former_member188493]

Separate fields/columns are possible using OPENXML.