SQLServer数据库提取XML文件内容

Question

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

Answer 1

可以使用XPath语法来筛选XML中的数据，具体脚本如下：

DECLARE @idoc int
DECLARE @xml xml

SELECT @xml=bulkcolumn FROM OPENROWSET( BULK 'C:\Users\Administrator\Desktop20160401_group_03.xml', SINGLE_BLOB) AS x

EXEC sp_xml_preparedocument @Idoc OUTPUT, @xml

SELECT *
FROM OPENXML (@Idoc, '/Message',2)
WITH (
    PhoneType varchar(30) '../PhoneInfo/PhoneType',
    RamUsage varchar(30) '../PhoneInfo/RamUsage',
    CpuUsage varchar(30) '../PhoneInfo/CpuUsage',
    NetType varchar(30) '../NetInfo/NetType',
    VideoTotleTraffic int '../TestResult/VideoTotleTraffic'
)
WHERE NetType = 'LTE'
AND VideoTotleTraffic > 1000

EXEC sp_xml_removedocument @idoc

这个脚本中使用了OPENXML函数和XPath语法，首先通过OPENXML将XML文件转换为表格形式，然后使用XPath语法来筛选需要的数据，最后使用WHERE语句来进一步筛选符合条件的数据。注意，由于VideoTotleTraffic是一个整数类型，所以在筛选时需要使用大于号（>），而不是等于号（=）。