第一步,
定义表变量,一会儿用来查询
<!--
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
,
分享到：
发表评论
评论