Home All advices
ENPL
Modify date: 2013-01-15 11:35:44

Extract data from xml

Below are shown how extract data from xml

example of a solution

SET @xml = '<leagues>
<league name="Bundesliga">
<teams>
    <team name="Bayer Leverkusen"/>
    <team name="Bayern Monachium"/>
    <team name="Borussia Dortmund"/>
    <team name="Schalke 04 Gelsenkirchen"/></teams></league>
<league name="Ligue 1"><teams>
    <team name="Girondins Bordeaux"/>
    <team name="Olympique Lyon"/>
    <team name="Olympique Marsylia"/>
    <team name="Paris Saint Germain"/></teams></league>
<league name="Premier League">
    <teams><team name="Arsenal Londyn"/>
    <team name="Chelsea Londyn"/>
    <team name="Liverpool FC"/><team name="Manchester City"/>
    <team name="Manchester United"/></teams></league>
<league name="Primera Division">
    <teams><team name="Atletico Madryt"/>
    <team name="FC Barcelona"/>
    <team name="Real Madryt"/>
    <team name="Valencia CF"/></teams></league>
<league name="Serie A"><teams>
    <team name="AC Milan"/>
    <team name="AS Roma"/>
    <team name="Inter Mediolan"/>
    <team name="Juventus FC"/>
    <team name="Lazio Rzym"/></teams></league>
</leagues>';
select
    (select ExtractValue(@xml, 'count(leagues/league)')) as number_leagues_v1,
    (select ExtractValue(@xml, 'count(//league)')) as number_leagues_v2,
    (select ExtractValue(@xml, 'count(//teams)')) as number_leagues_v3,
    (select ExtractValue(@xml, 'count(//teams/team)')) as number_of_teams_v1;

result table

number_leagues_v1number_leagues_v2number_leagues_v3number_of_teams_v1
55522
[ Strona główna ]