再来一个猛点的,优化XML读取,只优化即可,目前是3-4秒左右,能再优化下的答案+200分

kkun_3yue3 2009-04-26 04:40:47
再来一个猛点的,优化XML读取,只优化即可,目前是3-4秒左右,能再优化下的答案+200分
如能优化到毫秒级别再发贴+400分
测试的时候可能需要一些权限,读取本地XML文件

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

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
)
...全文
663 60 打赏 收藏 转发到动态 举报
写回复
用AI写文章
60 条回复
切换为时间正序
请发表友善的回复…
发表回复
xwb2766 2009-04-27
  • 打赏
  • 举报
回复
学习
laiahu 2009-04-27
  • 打赏
  • 举报
回复
学习..
冰岛男孩 2009-04-27
  • 打赏
  • 举报
回复
不错
EternalFaith 2009-04-27
  • 打赏
  • 举报
回复
这帖不学习不行
cycttt 2009-04-27
  • 打赏
  • 举报
回复
第二部呢
cool_hnu 2009-04-27
  • 打赏
  • 举报
回复
收藏~
zpphao 2009-04-26
  • 打赏
  • 举报
回复
路过..试了哈..
kkun_3yue3 2009-04-26
  • 打赏
  • 举报
回复
Garnett_KG大侠的结果测试有误,最后耗时再乘以10就对了,我写了除以10忘记去掉了,
不过至此仍然是毫秒级别,回贴说明一下,
qizhengsheng 2009-04-26
  • 打赏
  • 举报
回复
这个受藏了
fcuandy 2009-04-26
  • 打赏
  • 举报
回复
[Quote=引用 47 楼 kkun_3yue3 的回复:]
提示"元数据中不存在指定的集合:'SCwea'"
[/Quote]

不要用临时表,用实表,因为临时表存在于tempdb里。 创建schema时,元数据在当前库。
fcuandy 2009-04-26
  • 打赏
  • 举报
回复
[Quote=引用 46 楼 Garnett_KG 的回复:]
fc,我猜原来的慢是慢在CROSS APPLy那里,若是想办法不要做CROSS APPLY,那速度就上来了。

[/Quote]

不是, 那个cross apply左输入只有一行,不会有太多消耗的。
主要原因在于xml解析和拆分。
因为这,用了类型化xml才会有一半多的性能提升。

dom中应该是非托管类代码的处理(比较底层的算法),比起直接nodes,value之类这样的方式效率是好很多。

不用cross apply,速度只提高二三十ms. 相对于43楼的做法


DECLARE @TEMPWEA TABLE(WEATHERXML XML)
INSERT INTO @TEMPWEA
SELECT CAST(A AS XML) FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);

DECLARE @x XML(SCwea) -- or xml type
SELECT @x = WEATHERXML FROM @TEMPWEA

/* 将符合要求的城市天气预报放入表变量 */
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
@x.nodes('/DomesticCityWeatherTable/CityWeather') as t2(doc)

SELECT * FROM @BA_WEATHER

SELECT DATEDIFF(ms,@st,GETDATE())
kkun_3yue3 2009-04-26
  • 打赏
  • 举报
回复
提示"元数据中不存在指定的集合:'SCwea'"
Garnett_KG 2009-04-26
  • 打赏
  • 举报
回复
fc,我猜原来的慢是慢在CROSS APPLy那里,若是想办法不要做CROSS APPLY,那速度就上来了。
fcuandy 2009-04-26
  • 打赏
  • 举报
回复
最终,测试失败,

因为 sp_xml_preparedocument 不支持类型化xml.

KG的写法应该是根限了。500ms, 比之3400ms, 提高85%

不使用dom的话, t-sql实现,改用临时表+xml索引+类型化xml 结果为1600ms左右。提搞 53%
milk111 2009-04-26
  • 打赏
  • 举报
回复
顶下
fcuandy 2009-04-26
  • 打赏
  • 举报
回复
/* 将符合要求的城市天气预报放入表变量 */
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

TRUNCATE TABLE TEMPWEA
DROP TABLE TEMPWEA
SELECT DATEDIFF(ms,@st,GETDATE())
fcuandy 2009-04-26
  • 打赏
  • 举报
回复
转换为类型化xml,执行时间在1650-1760ms之间

DECLARE @st DATETIME
SET @st = GETDATE()
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
)


/* 加载本地数据 */
CREATE TABLE TEMPWEA(ID INT IDENTITY(1,1),WEATHERXML XML(SCwea))
INSERT TEMPWEA SELECT CAST(A AS XML) WEATHERXML FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);
--SELECT CAST(A AS XML) WEATHERXML,id=IDENTITY(INT) INTO TEMPWEA FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);

ALTER TABLE TEMPWEA ADD CONSTRAINT idx_p PRIMARY KEY (id)
CREATE PRIMARY XML INDEX idx_x_p ON TEMPWEA(WEATHERXML)
--CREATE XML INDEX idx_x_1 ON TEMPWEA(WEATHERXML)
--USING XML INDEX idx_x_p FOR PATH


fcuandy 2009-04-26
  • 打赏
  • 举报
回复
CREATE XML SCHEMA COLLECTION SCwea
AS '
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="DomesticCityWeatherTable">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="CityWeather" maxOccurs="unbounded">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>

<xsd:element name="CityID" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="CityName" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirst" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWeatherAbstract" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstHighTemperature" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstLowTemperature" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWindDirection" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWindPower" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateFirstBinqilin" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstBinqilinClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstBinqilinDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateFirstChuanyi" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstChuanyiClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstChuanyiDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateFirstTiganwendu" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstTiganwenduClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstTiganwenduDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateFirstWuran" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWuranClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWuranDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateFirstYinliao" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstYinliaoClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstYinliaoDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateFirstZiwaixian" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstZiwaixianClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstZiwaixianDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateSecond" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWeatherAbstract" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondHighTemperature" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondLowTemperature" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWindDirection" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWindPower" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateSecondBinqilin" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondBinqilinClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondBinqilinDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateSecondChuanyi" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondChuanyiClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondChuanyiDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateSecondTiganwendu" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondTiganwenduClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondTiganwenduDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateSecondWuran" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWuranClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWuranDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateSecondYinliao" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondYinliaoClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondYinliaoDescription" type="xsd:string" maxOccurs="unbounded" />

<xsd:element name="DateSecondZiwaixian" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondZiwaixianClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondZiwaixianDescription" type="xsd:string" maxOccurs="unbounded" />

</xsd:sequence>
<xsd:attribute name="newsid" type="xsd:string" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
加载更多回复(39)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧