我想提取XML文件内 X市下所有LTE类型 “PhoneInfo“和”NetInof“ 且“VideoTotleTraffic”>1000的数据。麻烦大神帮忙看下要怎么写脚本。
XML文件内容如下:
<?xml version="1.0" encoding="UTF-8"?>
<Message><PhoneInfo><PhoneType>A31c</PhoneType>
<OSVersion></OSVersion>
<BaseBand></BaseBand>
<Kernel></Kernel>
<InnerVersion></InnerVersion>
<RamUsage>75</RamUsage>
<CpuUsage>13</CpuUsage>
</PhoneInfo>
<PositionInfo><Longitude>116.62165</Longitude>
<Latitude>38.04869</Latitude>
<LocationDesc></LocationDesc>
<Province>J省</Province>
<City>A市</City>
</PositionInfo>
<NetInfo><NetType>LTE</NetType>
<APN></APN>
<dBm></dBm>
<Ci>24414258</Ci>
<Rsrp>-112.00</Rsrp>
<Sinr>3</Sinr>
<InnerIP>10.10.130.241</InnerIP>
<OuterIP>10.110.5.160</OuterIP>
</NetInfo>
<TestResult><VideoName>腾讯</VideoName>
<VideoURL>http://m.v.qq.com</VideoURL>
<VideoIP>106.38.181.141</VideoIP>
<VideoTestTime>2016-03-31 15:49:41.754</VideoTestTime>
<VideoAvgSpeed>1502.80</VideoAvgSpeed>
<VideoPeakSpeed>9756.20</VideoPeakSpeed>
<TCLASS></TCLASS>
<BufferCounter>0</BufferCounter>
<VideoSize></VideoSize>
<VideoTotleTraffic>1950247</VideoTotleTraffic>
</TestResult>
</Message>
SQLServer脚本如下:
DECLARE @idoc int
DECLARE @xml xml
SELECT @xml=bulkcolumn FROM OPENROWSET( BULK 'C:\Users\Administrator\Desktop20160401_group_03.xml', SINGLE_BLOB) AS x
SELECT @xml
EXEC sp_xml_preparedocument @Idoc OUTPUT, @xml
SELECT * into #temp FROM OPENXML (@Idoc, '/mediaData/Message/PhoneInfo',2)
WITH (
PhoneType varchar(30)
,RamUsage varchar(30)
,CpuUsage varchar(30)
)
select * from #temp
drop table #temp