XML读取超强优化

第一步,
定义表变量,一会儿用来查询

SQL code

<!-- 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 

SQL code

<!-- 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,