Jump to content

Modify .XML from DB SQL


Recommended Posts


I have a problem with an XML file that has the following structure:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> 
 <DATAPACKET Version="2.0">
 <METADATA><FIELDS>
 <FIELD attrname="Id" fieldtype="i4" readonly="true" SUBTYPE="Autoinc"/>
 <FIELD attrname="Blocco" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Domanda" fieldtype="string" WIDTH="2"/>
 <FIELD attrname="Risposta" fieldtype="boolean"/>
 <FIELD attrname="Capitolo" fieldtype="string" WIDTH="2"/>
 <FIELD attrname="Indice" fieldtype="string" WIDTH="3"/>
 <FIELD attrname="Argomento" fieldtype="string" WIDTH="1"/>
 <FIELD attrname="SubArgomento" fieldtype="string" WIDTH="2"/>
 <FIELD attrname="Figura" fieldtype="string" WIDTH="4"/>
 <FIELD attrname="FiguraBlk" fieldtype="string" WIDTH="4"/>
 <FIELD attrname="Difficolta" fieldtype="string" WIDTH="2"/>
 <FIELD attrname="Testo" fieldtype="string" WIDTH="320"/>
 <FIELD attrname="Lingua1" fieldtype="string" WIDTH="320"/>
 <FIELD attrname="Lingua2" fieldtype="string" WIDTH="320"/>
 <FIELD attrname="Lingua3" fieldtype="string" WIDTH="320"/>
 <FIELD attrname="Commento" fieldtype="string" WIDTH="256"/>
 <FIELD attrname="Aiuto" fieldtype="string" WIDTH="128"/>
 <FIELD attrname="Foto1" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Foto2" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Foto3" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Foto4" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Foto5" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Video1" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Edl1" fieldtype="string" WIDTH="15"/>
 <FIELD attrname="Video2" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Edl2" fieldtype="string" WIDTH="15"/>
 <FIELD attrname="Video3" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Edl3" fieldtype="string" WIDTH="15"/>
 <FIELD attrname="Audio1" fieldtype="string" WIDTH="12"/>
 <FIELD attrname="Audio2" fieldtype="string" WIDTH="12"/>
 <FIELD attrname="Audio3" fieldtype="string" WIDTH="12"/>
 <FIELD attrname="Html1" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Html2" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Html3" fieldtype="string" WIDTH="5"/><FIELD attrname="Libro1" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Libro1PosY" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Libro2" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Libro2PosY" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Libro3" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Libro3PosY" fieldtype="string" WIDTH="5"/>
 <FIELD attrname="Info1" fieldtype="string" WIDTH="120"/>
 <FIELD attrname="Info2" fieldtype="string" WIDTH="120"/>
 <FIELD attrname="Gruppo1" fieldtype="string" WIDTH="3"/>
 <FIELD attrname="Gruppo2" fieldtype="string" WIDTH="3"/>
 <FIELD attrname="Gruppo3" fieldtype="string" WIDTH="3"/>
 </FIELDS><PARAMS AUTOINCVALUE="7166"/></METADATA>
 <ROWDATA>
 <ROW Id="2" Blocco="11023" Domanda="02" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="6" Testo="I ciclomotori possono avere due o tre ruote" Lingua1="Les motocycles légers peuvent avoir deux ou trois roues" Lingua2="Kleinkrafträder können zwei oder drei Räder haben" Lingua3="I ciclomotori possono avere due o tre ruote" Commento="infatti i CICLOMOTORI possono avere DUE, TRE e anche QUATTRO RUOTE, cilindrata fino a 50 cm³ e velocità fino a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="3113" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40231" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/>
 <ROW Id="3" Blocco="11023" Domanda="03" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="5" Testo="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Lingua1="Pas tous les véhicules à moteur à deux roues peuvent être classifiés des motocycles légers" Lingua2="Nicht alle zweirädrigen Kraftfahrzeuge werden als Kleinkrafträder eingestuft" Lingua3="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Commento="infatti vengono CLASSIFICATI CICLOMOTORI solo i veicoli a DUE RUOTE con CILINDRATA NON SUPERIORE a 50 cm³ e VELOCITÀ NON SUPERIORE a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="1238" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40232" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/>


it continues with this structure but it is very long.


How can I do from my SQL DB to extract a data ("Libro3")  to insert it inside every occurrence of  ''Libro3' of XML file?

In my sql to recognize the line to be modified I have Id,Blocco, Libro3 obviously, but i don t know how i can modify the file.

to recognize the line to be modified on the sql I have line, id and block

Link to post
Share on other sites

SQL's only purpose is to manage databases, it cannot read or write to files. You need a separate programming language to do that.

If you are working in PHP, you can extract the data from the database the normal way, then use DOMDocument to edit the XML and save it to a file.

Link to post
Share on other sites
   
$conn = myDB::open();


$sql="SELECT `Id`,`Blocco`,`Libro3` FROM `quiz` WHERE `listato` LIKE 'AB1' ORDER BY `quiz`.`Libro3` ASC";


$result = $conn->query($sql);

$xml=simplexml_load_file("NQM2016.1.XML");

$array=json_encode($xml);

This is my PHP Code for extracting data but now how can i do to modify the "Libro3" data in XML?

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...