Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

Html2Db: Co się stało z reprezentacją Kenii?

Na wss.pl pojawił się wątek dotyczący przekształcenia danych tabelarycznych ze strony HTML na tabelę w bazie danych. W dyskusji wyraziłem wątpliwość co do trywialności rozwiązania ze względu na fakt, że zawartość stron HTML odbiega znacznie od poprawnych dokumentów XML (a do zapisu do bazy danych chciałem wykorzystać możliwości XML w SQL Server). Jednak jak się okazało istnieje świetny helper do dokumentów HTML, który znajduje się pod tym adresem: htmlagilitypack.

Jako cel swego przekształcenia wybrałem ranking FIVB drużyn męskich.

Dzięki zastosowaniu helpera przekształcenie response HTML w XML jest banalnie proste:

WebClient wc = new WebClient();
byte[] bytes = wc.DownloadData("http://www.fivb.org/en/volleyball/Rankings/Rank_men_2009_11.asp");

UTF8Encoding utf8 = new UTF8Encoding();
string response = utf8.GetString(bytes);

HtmlDocument doc = new HtmlDocument();
doc.LoadHtml(response);
doc.OptionOutputAsXml = true;
doc.Save(@"d:\wymiana\fivb.xml");



Po przekształceniu w dokument XML i po zwinięciu nieistotnych elementów strony docieramy do istotnych danych tabelarycznych zawierających ranking:



Teraz tylko jeszcze jedno "wygładzające" przekształcenie w zupełnie czysty i czytelny dokument zawierający wyłącznie dane rankingowe: nazwa drużyny, aktualne miejsce, poprzednie miejsce oraz ilość punktów (nie jest to konieczne, SQL Serer poradziłby sobie i z takim dokumentem):

HtmlDocument doc = new HtmlDocument();
doc.Load(@"d:\wymiana\fivb.xml");

HtmlNodeCollection nodes = doc.DocumentNode.SelectNodes("/span/html/body/center/table[4]/tr[1]/td[2]/table[1]/tr[5]/td[1]/table[1]/tr");

HtmlDocument output = new HtmlDocument();

HtmlNode ranking = output.CreateElement("ranking");

int index = 0;
foreach (HtmlNode node in nodes)
{
    if (index > 3)
    {
        HtmlNode n = output.CreateElement("team");
        HtmlNode name = output.CreateElement("name");
        HtmlNode current = output.CreateElement("current");
        HtmlNode previous = output.CreateElement("previous");
        HtmlNode points = output.CreateElement("points");

        name.InnerHtml = node.ChildNodes[7].InnerText;
        current.InnerHtml = node.ChildNodes[3].InnerText;
        previous.InnerHtml = node.ChildNodes[5].InnerText;
        points.InnerHtml = node.ChildNodes[9].InnerText;

        n.AppendChild(name);
        n.AppendChild(current);
        n.AppendChild(previous);
        n.AppendChild(points);

        ranking.AppendChild(n);
    }

    index++;
}

output.DocumentNode.AppendChild(ranking);

File.Delete(@"d:\wymiana\ranking.xml");

output.Save(@"d:\wymiana\ranking.xml");

I teraz możemy przepompować ranking z dokumentu XML do tabeli SQL Server:

CREATE TABLE #t (html nvarchar(max))

BULK INSERT #t FROM 'D:\Wymiana\ranking.xml'

DECLARE @txt nvarchar(max) = ''

SELECT @txt = @txt + ' ' + ISNULL(html, '') FROM #t

SET @txt = SUBSTRING(@txt,2,2147483647)

DECLARE @xml xml = CAST(@txt AS xml)

DECLARE @t table(current_rank int, previous_rank int, team nvarchar(100), points numeric)

INSERT INTO @t
SELECT    t.c.value('(./current)[1]', 'int') current_rank,
        replace(
            replace(
                replace(t.c.value('(./previous)[1]', 'nvarchar(10)'),'(','')
            ,')',''),
        'N.Rkd','') previous_rank,
        t.c.value('(./name)[1]', 'nvarchar(100)') team,
        t.c.value('(./points)[1]', 'numeric') total
FROM @xml.nodes('/ranking/team') t(c)

UPDATE @t SET previous_rank = (SELECT MAX(current_rank)+1 FROM @t) WHERE previous_rank = 0

SELECT previous_rank-current_rank, team from @t order by 1

DROP TABLE #t


Zrobiłem ten ranking i okazało się, że największy spadek w rankingu zanotowała reprezentacja Kenii, bo spadła aż o 73 miejsca. Ale nic nie byłoby w tym dziwnego gdyby nie fakt, że obecnie ma zero punktów.

W takim razie ile miała w poprzednim notowaniu gdy była na 43 miejscu? Czyżby jakaś kara czy może tylko błąd wprowadzania danych?

Edit:

Pisałem powyżej, że kod XML otrzymany z helpera można bezpośrednio wykorzystać w kodzie T-SQL. Aby nie być gołosłownym podaje ten kod:
INSERT INTO @t
SELECT    t.c.value('(./td)[2]', 'int') current_rank,
        replace(
            replace(
                replace(t.c.value('(./td)[3]', 'nvarchar(10)'),'(','')
            ,')',''),
        'N.Rkd','') previous_rank,
        t.c.value('(./td)[4]', 'nvarchar(100)') team,
        t.c.value('(./td)[5]', 'numeric') total
FROM @xml.nodes('/span/html/body/center/table[4]/tr[1]/td[2]/table[1]/tr[5]/td[1]/table[1]/tr[fn:position() gt 4]') t(c)

Jak widać po tych przykładach przekształceń przetworzenie kodu HTML zawierającego dane tabelaryczne wcale nie jest trudne.
Opublikowane 7 grudnia 2009 21:26 przez marekpow

Komentarze:

 

dev2dev said:

Ostatnio na forum wss.pl było sporo wątków na temat niejawnych konwersji dokonywanych przez SQL Server

grudnia 8, 2009 09:29
 

dotnetomaniak.pl said:

Dziękujemy za publikację - Trackback z dotnetomaniak.pl

grudnia 8, 2009 10:14
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems