Xpath i Mysql

Fra Harald Grovens wiki

Gå til: navigasjon, søk

Kodesnutter til kjappkurs i Mysqls Xpath-funksjoner


Innhold

[rediger] Testdatasett

Testdatasett henta fra Wikipedias liste over Norske kommuner

Kan lastes ned som tekstfil fra Wpkommuner.sql.

Øvinger til fila "Norske kommuner"

Klikk pÃ¥ rediger for Ã¥ se fasit :-)

Her er strukturen på hver av radene i datasettet: 

<tr>

  <td>1902</td>
  <td>
    <a href="http://no.wikipedia.org/wiki/Troms%C3%B8" title="Tromsø">Tromsø</a>
  </td>
  <td>
    <a href="http://no.wikipedia.org/wiki/Troms%C3%B8" title="Tromsø">Tromsø</a>
  </td>
  <td>
    <a href="http://no.wikipedia.org/wiki/Troms" title="Troms">Troms</a>
  </td>
  <td>
    <span style="display:none">3&504&66003&</span>66 003
  </td>
  <td>2566,26</td>
  
  <td>
    <div class="center">
      <div class="floatnone">
	<span>
	  <a href="http://no.wikipedia.org/wiki/Bilde:NO_1902_Troms%C3%B8.svg" class="image" title="NO 1902 Tromsø.svg"
	    <img alt="" src="http://upload.wikimedia.org/wikipedia/commons/thumb/1/1f/NO_1902_Troms%C3%B8.svg/45px-NO_1902_Troms%C3%B8.svg.png" 
	    width="45" height="36" border="0" /></a>
	</span>
      </div>
    </div>
  </td>

  <td>
    <a href="http://no.wikipedia.org/wiki/Bilde:Coat_of_arms_of_Troms%C3%B8.svg" class="image" title="Coat of arms of Tromsø.svg">
      <img alt="" src="http://upload.wikimedia.org/wikipedia/commons/thumb/6/67/Coat_of_arms_of_Troms%C3%B8.svg/46px-Coat_of_arms_of_Troms%C3%B8.svg.png" width="46" height="72" border="0" />
    </a>
  </td>

  <td>Nøytral</td>

  <td>
    <a href="http://no.wikipedia.org/wiki/Arild_Hausberg" title="Arild Hausberg">Arild Hausberg</a>
  </td>

  <td>
    <a href="http://no.wikipedia.org/wiki/Det_norske_Arbeiderparti" title="Det norske Arbeiderparti">Ap</a>
  </td>

</tr>


[rediger] Lag liste over alle 430 kommunenummer

Det obligatoriske "hello world"-eksemplet


[rediger] List opp navn på alle kommunene i Nord-Norge

Hint: De to første siffer angir fylkesnummer i kommunenummeret, og du kan filtrere på to siffer til venstre


[rediger] List opp kommunenavn og URL til alle kommunevåpnene i Troms og Møre og Romsdal

Møre er fylkesnr 15, Troms nr 19. Gjøres i én spørring


[rediger] List opp partier og ordførere for alle Nord-Norske kommuner

Hent fullt navn, ikke forkortelse for kommunenavn. Sorter etter fylke, deretter kommunenavn

Hint du kan sortere etter Xpath funksjoner


[rediger] Lag en kommaseparert liste over hvilke kommuner som er styrt av hvilke partier

Bruk GROUP_CONCAT(), og grupper etter xpathfunksjon som henter ut parti

For Ã¥ unngÃ¥ at lista blir trunkert, mÃ¥ du sette SET GROUP_CONCAT_MAX_LEN = 5000 ;


[rediger] Importskriptsgenereringsskript 



-- lage generisk importskript
-- lage generisk importskript
-- bytt to variabler (databasenavn og tabellnavn) og eksporter som CSV med blankt som LINES/FILEDS TERMINATED BY
 
SELECT
 
CONCAT('\n-- ', `TABLE_NAME` , '\n'
'INSERT INTO ', `TABLE_SCHEMA` , '.', `TABLE_NAME` ,
'\n\nSELECT \n\t\'\' AS ',
GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ', \n\t\'\' AS ' ),
'\nFROM ', `TABLE_SCHEMA` , '.', `TABLE_NAME` ,
'\n; \n' )  
AS createstatement
 
FROM information_schema.COLUMNS
 
-- sett inn navn på database som skal
WHERE TABLE_SCHEMA IN ('drupal')
 
-- sett inn liste over tabeller det skal lages skript for
AND TABLE_NAME IN ('node', 'node_revisions', 'url_alias')
 
GROUP BY TABLE_NAME
;

Eksempel-output fra drupals viktigste tabeller 



-- node
INSERT INTO utdno2.node

SELECT 
	'' AS nid
	'' AS vid
	'' AS type
	'' AS title
	'' AS uid
	'' AS status
	'' AS created
	'' AS changed
	'' AS comment
	'' AS promote
	'' AS moderate
	'' AS stickyutdno2_geo.node
FROM <tabell>
; 


-- node_revisions
INSERT INTO utdno2.node_revisions

SELECT 
	'' AS nid
	'' AS vid
	'' AS uid
	'' AS title
	'' AS body
	'' AS teaser
	'' AS log
	'' AS timestamp
	'' AS formatutdno2_geo.node_revisions
FROM <tabell>
; 


-- url_alias
INSERT INTO utdno2.url_alias

SELECT 
	'' AS pid
	'' AS src
	'' AS dstutdno2_geo.url_alias
FROM <tabell>
;

[rediger] Shell skript for skjermskraping 

wget -O - http://www.samordnaopptak.no/restetorget/studiumliste.html >> /home/harald/tmp/so.html



wget -O - http://www.samordnaopptak.no/restetorget/studiumliste.html | 
tidy --add-xml-decl y --bare y --clean y --hide-comments y --output-xhtml y --show-body-only y --indent "auto" --wrap "90" --char-encoding "utf8" --input-encoding "utf8" --output-encoding "utf8" >> /home/harald/tmp/so.html


mysql -e 'TRUNCATE test.so ; '  test ;

mysql -e 'LOAD DATA INFILE "/home/harald/tmp/so.html" INTO TABLE `so` LINES TERMINATED BY "</tr>" ; '  test ;

mysql -e 'UPDATE test.so SET test=REPLACE(REPLACE(REPLACE(test,"\n",""),"\r",""),"\t","")  ; '  test ;
mysql -e 'UPDATE test.so SET test=CONCAT(test," </tr> ") ; '  test ;
mysql -e 'UPDATE test.so SET test=TRIM(REPLACE(REPLACE(REPLACE(test,"href= ","href="),"  ",""),"<td","\n<td"))  ; '  test ;


mysql -e <<<EOF 
CREATE TABLE test.so_data

SELECT
(SELECT DISTINCT EXTRACTVALUE(`test`,'/tr/td[2]') FROM `so` WHERE test LIKE '%Siste endring skjedde%') AS updated,
-- (SELECT EXTRACTVALUE(`test`,'/tr/td[2]') FROM `so` WHERE test LIKE '%Antall studier med ledige plasser nå%') AS ledige,
EXTRACTVALUE(`test`,'/tr/td[1]/a') AS studkode,
EXTRACTVALUE(`test`,'/tr/td[2]/a') AS studium,
EXTRACTVALUE(`test`,'/tr/td[3]/a') AS kravkode,
EXTRACTVALUE(`test`,'/tr/td[4]') AS antallnyesokere,
CONCAT(EXTRACTVALUE(`test`,'/tr/td[5]/a'),'',EXTRACTVALUE(`test`,'/tr/td[5]')) AS kontaktinfo,
REPLACE(EXTRACTVALUE(`test`,'/tr/td[6]'),' ',' ') AS paalisten

FROM test.`so`
WHERE EXTRACTVALUE(`test`,'/tr/td[3]/a')<>''
;
EOF
Hentet fra «http://www.groven.no/harald/Xpath_i_Mysql»