jix1031
Lastjix
2016-04-17 06:25

SQLServer数据库提取XML文件内容

我想提取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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

相关推荐