Import website XML-feed to SQL Server

Question

Hi All,

Hope you all are doing well.

I need to import an XML-feed from a website to my SQL Server database. I don't know much about XML.

The feed structure is an bit complex. Here is the sample of that file:

<line_feed>
<FeedTime>1279519582927</FeedTime>
<lastContest>4103839</lastContest>
<lastGame>58629754</lastGame>
<events>
<event>
    <event_datetimeGMT>2010-07-19 21:30</event_datetimeGMT>
    <gamenumber>174087393</gamenumber>
    <sporttype>Tennis</sporttype>
    <league>abc</league>
    <participants>
        <participant>
            <participant_name>R. Ram</participant_name>
            <contestantnum>4303</contestantnum>
            <rotnum>4303</rotnum>
            <visiting_home_draw>Visiting</visiting_home_draw>
        </participant>
        <participant>
            <participant_name>K. Beck</participant_name>
            <contestantnum>4304</contestantnum>
            <rotnum>4304</rotnum>
            <visiting_home_draw>Home</visiting_home_draw>
        </participant>
    </participants>
    <periods>
        <period>
            <period_number>0</period_number>
            <period_description>Game</period_description>
            <periodcutoff_datetimeGMT>2010-07-19 21:30</periodcutoff_datetimeGMT>
            <period_status>I</period_status>
            <period_update>open</period_update>
            <spread_maximum>250</spread_maximum>
            <moneyline_maximum>1500</moneyline_maximum>
            <total_maximum>250</total_maximum>
            <moneyline>
                <moneyline_visiting>135</moneyline_visiting>
                <moneyline_home>-146</moneyline_home>
            </moneyline>
        </period>
        <period>
            <period_number>0</period_number>
            <period_description>Game</period_description>
            <periodcutoff_datetimeGMT>2010-07-19 21:30</periodcutoff_datetimeGMT>
            <period_status>I</period_status>
            <period_update>open</period_update>
            <spread_maximum>250</spread_maximum>
            <moneyline_maximum>250</moneyline_maximum>
            <total_maximum>250</total_maximum>
            <spread>
                <spread_visiting>2</spread_visiting>
                <spread_adjust_visiting>101</spread_adjust_visiting>
                <spread_home>-2</spread_home>
                <spread_adjust_home>-118</spread_adjust_home>
            </spread>
            <total>
                <total_points>22.5</total_points>
                <over_adjust>-108</over_adjust>
                <under_adjust>-108</under_adjust>
            </total>
        </period>
        <period>
            <period_number>1</period_number>
            <period_description>1st Set</period_description>
            <periodcutoff_datetimeGMT>2010-07-19 21:30</periodcutoff_datetimeGMT>
            <period_status>I</period_status>
            <period_update>open</period_update>
            <spread_maximum>5000</spread_maximum>
            <moneyline_maximum>250</moneyline_maximum>
            <total_maximum>5000</total_maximum>
            <moneyline>
                <moneyline_visiting>114</moneyline_visiting>
                <moneyline_home>-133</moneyline_home>
            </moneyline>
        </period>
    </periods>
</event>
</events>
</line_feed>

Can you please help me out, how can I approach to load the data from that feed to SQL Server.

Please also ask me if any other details needed to understand this scenario. Awaiting your kind response.

Thank You, Prashant

Answer 1

If your XML is wel-formed, you can store it in an xml type variable. Then you can use XPath to read fields from it:

declare @xml xml
set @xml = '
<line_feed>
<PinnacleFeedTime>1279519582927</PinnacleFeedTime>
...
'

select @xml.value('(line_feed/events/event/sporttype)[1]', 'VARCHAR(8000)')

This would print Tennis.

Answer 2

It totally depends on what you want to extract from your feed....

I'm assuming here that you have your XML stored in a SQL Server variable like this:

DECLARE @input XML 

This query here will enumerate over all <participants> nodes and extract all information about a single participant - you could use this to insert those participants into a table:

SELECT
    Feed.Participant.value('(participant_name)[1]', 'varchar(50)') AS ParticipantName,
    Feed.Participant.value('(contestantnum)[1]', 'int') AS ContestantNum,
    Feed.Participant.value('(rotnum)[1]', 'int') AS RotNum,
    Feed.Participant.value('(visiting_home_draw)[1]', 'varchar(50)') AS VisitingHome
FROM
    @input.nodes('/line_feed/events/event/participants/participant') AS Feed(Participant)

Output:

ParticipantName ContestantNum   RotNum  VisitingHome
R. Ram                  4303        4303     Visiting
K. Beck                 4304        4304     Home

This second query enumerates the <period> nodes in your feed - again, it extracts some information, which you could use to store in a database table:

SELECT
    Feed.Period.value('(period_number)[1]', 'int') AS PeriodNumber,
    Feed.Period.value('(period_description)[1]', 'varchar(50)') AS PeriodDescription,
    Feed.Period.value('(period_status)[1]', 'varchar(50)') AS Status,
    Feed.Period.value('(period_update)[1]', 'varchar(50)') AS Update,
    Feed.Period.value('(moneyline_maximum)[1]', 'decimal(18,4)') AS MoneylineMaximum
FROM
    @input.nodes('/line_feed/events/event/periods/period') AS Feed(Period)

Output:

PeriodNumber   PeriodDescription   Status  Update  MoneylineMaximum
    0             Game                I     open      1500.0000
    0             Game                I     open       250.0000
    1             1st Set             I     open       250.0000