問題描述

希望你們一切順利.

我需要將 XML 源從網站導入到我的 SQL Server 數據庫.我對 XML 不太了解.

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

Feed 結構有點復雜.這是該文件的示例:

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>

能否請您幫幫我，我該如何將數據從該提要加載到 SQL Server.

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.

謝謝，普拉桑特

推薦答案

如果您的 XML 格式良好，您可以將其存儲在 xml 類型變量中.然后你可以使用 XPath 從中讀取字段:

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)')

這將打印Tennis.

這篇關于將網站 XML-feed 導入 SQL Server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！