第一步,
定義表變量,一會兒用來查詢
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
DECLARE
@BA_WEATHER
TABLE
(
[
CityID
]
[
nvarchar
]
(
20
)
NULL
,
[
CityName
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirst
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWeatherAbstract
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstHighTemperature
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstLowTemperature
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWindDirection
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWindPower
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstBinqilin
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstBinqilinClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstBinqilinDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstChuanyi
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstChuanyiClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstChuanyiDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstTiganwendu
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstTiganwenduClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstTiganwenduDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWuran
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWuranClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWuranDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstYinliao
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstYinliaoClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstYinliaoDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstZiwaixian
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstZiwaixianClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstZiwaixianDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecond
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondWeatherAbstract
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondHighTemperature
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondLowTemperature
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondWindDirection
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondWindPower
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondBinqilin
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondBinqilinClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondBinqilinDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondChuanyi
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondChuanyiClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondChuanyiDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondTiganwendu
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondTiganwenduClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondTiganwenduDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondWuran
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondWuranClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondWuranDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondYinliao
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondYinliaoClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondYinliaoDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondZiwaixian
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondZiwaixianClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateSecondZiwaixianDescription
]
[
nvarchar
]
(
20
)
NULL
)
優化下邊的代碼,目前執行是3-4秒
下載測試文件
http://tstring.com.cn/i/domesticcityweather.xml
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
/*
加載本地數據
*/
DECLARE
@TEMPWEA
TABLE
(WEATHERXML XML)
INSERT
INTO
@TEMPWEA
SELECT
CAST
(A
AS
XML)
FROM
OPENROWSET
(
BULK
N
'
d:\DomesticCityWeather.xml
'
,SINGLE_BLOB) A(A);
/*
將符合要求的城市天氣預報放入表變量
*/
INSERT
INTO
@BA_WEATHER
SELECT
t2.doc.value(
'
(CityID)[1]
'
,
'
nvarchar(20)
'
)
AS
CityID, t2.doc.value(
'
(CityName)[1]
'
,
'
nvarchar(20)
'
)
AS
CityName,
/*
第一天所有參數
*/
t2.doc.value(
'
(DateFirst)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirst, t2.doc.value(
'
(DateFirstWeatherAbstract)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstWeatherAbstract, t2.doc.value(
'
(DateFirstHighTemperature)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstHighTemperature, t2.doc.value(
'
(DateFirstLowTemperature)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstLowTemperature, t2.doc.value(
'
(DateFirstWindDirection)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstWindDirection, t2.doc.value(
'
(DateFirstWindPower)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstWindPower,
/*
冰淇凌指數
*/
t2.doc.value(
'
(DateFirstBinqilin)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstBinqilin, t2.doc.value(
'
(DateFirstBinqilinClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstBinqilinClass, t2.doc.value(
'
(DateFirstBinqilinDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstBinqilinDescription,
/*
穿衣指數
*/
t2.doc.value(
'
(DateFirstChuanyi)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstChuanyi, t2.doc.value(
'
(DateFirstChuanyiClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstChuanyiClass, t2.doc.value(
'
(DateFirstChuanyiDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstChuanyiDescription,
/*
體感溫度（舒適指數）
*/
t2.doc.value(
'
(DateFirstTiganwendu)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstTiganwendu, t2.doc.value(
'
(DateFirstTiganwenduClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstTiganwenduClass, t2.doc.value(
'
(DateFirstTiganwenduDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstTiganwenduDescription,
/*
污染指數
*/
t2.doc.value(
'
(DateFirstWuran)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstWuran, t2.doc.value(
'
(DateFirstWuranClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstWuranClass, t2.doc.value(
'
(DateFirstWuranDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstWuranDescription,
/*
飲料指數
*/
t2.doc.value(
'
(DateFirstYinliao)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstYinliao, t2.doc.value(
'
(DateFirstYinliaoClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstYinliaoClass, t2.doc.value(
'
(DateFirstYinliaoDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstYinliaoDescription,
/*
紫外線指數
*/
t2.doc.value(
'
(DateFirstZiwaixian)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstZiwaixian, t2.doc.value(
'
(DateFirstZiwaixianClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstZiwaixianClass, t2.doc.value(
'
(DateFirstZiwaixianDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateFirstZiwaixianDescription,
/*
第二天所有參數
*/
t2.doc.value(
'
(DateSecond)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecond, t2.doc.value(
'
(DateSecondWeatherAbstract)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondWeatherAbstract, t2.doc.value(
'
(DateSecondHighTemperature)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondHighTemperature, t2.doc.value(
'
(DateSecondLowTemperature)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondLowTemperature, t2.doc.value(
'
(DateSecondWindDirection)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondWindDirection, t2.doc.value(
'
(DateSecondWindPower)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondWindPower,
/*
冰淇凌指數
*/
t2.doc.value(
'
(DateSecondBinqilin)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondBinqilin, t2.doc.value(
'
(DateSecondBinqilinClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondBinqilinClass, t2.doc.value(
'
(DateSecondBinqilinDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondBinqilinDescription,
/*
穿衣指數
*/
t2.doc.value(
'
(DateSecondChuanyi)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondChuanyi, t2.doc.value(
'
(DateSecondChuanyiClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondChuanyiClass, t2.doc.value(
'
(DateSecondChuanyiDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondChuanyiDescription,
/*
體感溫度（舒適指數）
*/
t2.doc.value(
'
(DateSecondTiganwendu)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondTiganwendu, t2.doc.value(
'
(DateSecondTiganwenduClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondTiganwenduClass, t2.doc.value(
'
(DateSecondTiganwenduDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondTiganwenduDescription,
/*
污染指數
*/
t2.doc.value(
'
(DateSecondWuran)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondWuran, t2.doc.value(
'
(DateSecondWuranClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondWuranClass, t2.doc.value(
'
(DateSecondWuranDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondWuranDescription,
/*
飲料指數
*/
t2.doc.value(
'
(DateSecondYinliao)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondYinliao, t2.doc.value(
'
(DateSecondYinliaoClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondYinliaoClass, t2.doc.value(
'
(DateSecondYinliaoDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondYinliaoDescription,
/*
紫外線指數
*/
t2.doc.value(
'
(DateSecondZiwaixian)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondZiwaixian, t2.doc.value(
'
(DateSecondZiwaixianClass)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondZiwaixianClass, t2.doc.value(
'
(DateSecondZiwaixianDescription)[1]
'
,
'
nvarchar(20)
'
)
AS
DateSecondZiwaixianDescription
FROM
(
SELECT
WEATHERXML
FROM
@TEMPWEA
) c
CROSS
apply WEATHERXML.nodes(
'
/DomesticCityWeatherTable/CityWeather
'
)
as
t2(doc)
SELECT
*
FROM
@BA_WEATHER
---------------------------------------------------------
以下是優化后的代碼:
IF
OBJECT_ID
(
'
TEMPDB..#BA_WEATHER
'
)
IS
NOT
NULL
DROP
TABLE
#BA_WEATHER
GO
CREATE
TABLE
#BA_WEATHER (
[
CityID
]
[
nvarchar
]
(
20
)
NULL
,
[
CityName
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirst
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWeatherAbstract
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstHighTemperature
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstLowTemperature
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWindDirection
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstWindPower
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstBinqilin
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstBinqilinClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstBinqilinDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstChuanyi
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstChuanyiClass
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstChuanyiDescription
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstTiganwendu
]
[
nvarchar
]
(
20
)
NULL
,
[
DateFirstTiganwenduClass
]
[
nvarchar
]
(
20
)
NULL
,
分享到：
發表評論
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
評論