OPENXML是一个rowset函数,它的工作方式类似于rowset函数OPENQUERY和OPENROWSET。使用OPENXML可以对XML数据执行JOINs操作而无需首先导入数据。你还可以将其同INSERT、SELECT、UPDATE以及DELETE等操作联合使用。

有时会在存储过程中处理一些XML格式的数据,所以会用到sp_xml_preparedocument,他可以将XML数据进行读取,然后使用
MSXML 分析器 (Msxmlsql.dll)
对其进行分析。我们就可以很容易的在存储过程中得到XML中我们想要的数据。下面的代码就是使用sp_xml_preparedocument读取XML:复制代码 代码如下: DECLARE @hdoc int DECLARE
@doc varchar(1000) SET @doc =’ ROOT Customer CustomerID=”VINET”
ContactName=”Paul Henriot” Order CustomerID=”VINET” EmployeeID=”5″
OrderDate=”1996-07-04T00:00:00″ OrderDetail OrderID=”10248″
ProductID=”11″ Quantity=”12″/ OrderDetail OrderID=”10248″ ProductID=”42″
Quantity=”10″/ /Order /Customer Customer CustomerID=”LILAS”
ContactName=”Carlos Gonzlez” Order CustomerID=”LILAS” EmployeeID=”3″
OrderDate=”1996-08-16T00:00:00″ OrderDetail OrderID=”10283″
ProductID=”72″ Quantity=”3″/ /Order /Customer /ROOT’ EXEC
sp_xml_preparedocument @hdoc OUTPUT, @doc
上面只是读取了XML,要想获取XML数据还需要使用OPENXML,代码如下: 复制代码 代码如下: SELECT * FROM
openxml(@hdoc,’/ROOT/Customer’,1) WITH (CustomerID
VARCHAR(40),ContactName VARCHAR(40)) OPENXML有三个参数:
第一个是sp_xml_preparedocument读取是的OUTPUT参数,在本示例中就是@hdoc;
第二个是一个XPath表达式,用来获取指定位置的数据;
第三个是一个可选项,用来表示获取的方式,有0,1,2,8四种取值,详细解释请看
FROM后面的WITH也是可选的,用来指定获取哪些数据字段,上面代码中只取了CustomerID和ContactName。上面的查询结果如下:
CustomerID ContactName —————————————- —————————————- VINET Paul Henriot
LILAS Carlos Gonzlez
如果不指定WITH子句,查询出来的是一个默认的表结构,如下:

1.Download StackExchange’s open data
https://archive.org/details/stackexchange

–DECLARE @xml varchar(1024) –SET @xml=’ –<root> — <code
id=”030001″> —  <point id=”01″ /> —  <point id=”02″ />
— </code> –</root>’ –DECLARE @hdoc int –EXEC
sp_xml_preparedocument @hdoc out,@xml –SELECT * FROM
–OPENXML(@hdoc, ‘/root/code/point’, 2)  –WITH  –(  —  itemCode
varchar(36) ‘../@id’, —  point varchar(36) ‘@id’ –) AS a —-在这里的
结果集 a 就包含了主从表的信息 —-INSERT INTO TABLEa SELECT * FROM a
–EXEC sp_xml_removedocument @hdoc

SQLServer数据库提取XML文件内容
我想提取XML文件内 X市下所有LTE类型 “PhoneInfo“和”NetInof“
且“VideoTotleTraffic”>1000的数据。麻烦大神帮忙看下要怎么写脚本。
XML文件内容如下:

然而,要使用OPENXML,你必须执行两项OPENQUERY和OPENROWSET并不需要的任务。这两项任务需要两个系统存储进程。

表格列的解释说明:

2.Importing and Process data from XML files into SQL Server tables
https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

DECLARE @xml varchar(1024) SET @xml=’ <root>  <code
id=”030001″>   <point id=”01″ />   <point id=”02″ />
 </code> </root>’ DECLARE @hdoc int EXEC
sp_xml_preparedocument @hdoc out,@xml SELECT * FROM OPENXML(@hdoc,
‘/root/code/point’, 2)  WITH  (    itemCode varchar(36) ‘../@id’,  
point varchar(36) ‘@id’ ) AS a –在这里的 结果集 a 就包含了主从表的信息
–INSERT INTO TABLEa SELECT * FROM a EXEC sp_xml_removedocument @hdoc

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

第一个是sp_xml_preparedocument,它将读取特定的XML文本并将其内容提取到内存中。其语法如下:

列名

step1.Importing XML data from XML file using OPENROWSET
对于第一个脚本,第一个创建数据库的命令我选择了注释掉改为手动创建该数据库,因为使用该命令创建的权限方面貌似有点问题。这个阶段遇到的第二个问题是导入的xml文件过大,sql
server默认的配置是导入的xml文件有2M限制,在工具-选项里可以设置一下,设置完毕记得重启sql
server。虽然这个问题解决了,但在导入400M左右的xml文件时,sql
server报“System.OutOfMemoryException”的异常,猜测可能跟xml文件有关,因为在跑普通的table时,4G左右的表也没有出现这个问题,暂时先不解决了。

exec [dbo].SkyProcessXMLData   ‘ <DataSet>   <tblEmp>    
<name>Vishal</name>    
<designation>Developer</designation>l   </tblEmp>  
<tblEmp>     <name>Jibin</name>    
<designation>System Analyst</designation>l   </tblEmp>
</DataSet>’

SQLServer脚本如下:
DECLARE @idoc int

代码如下复制代码

数据类型

step2.Process XML data using OPENXML function

 

DECLARE @xml xml

sp_xml_preparedocument @hdoc = OUTPUT,

说明

First call the sp_xml_preparedocument stored procedure by
specifying the XML data which will then output the handle of the XML
data that it has prepared and stored in internal cache.Then we will use
the handle returned by the sp_xml_preparedocument stored procedure
in the OPENXML function to open the XML data and read it.
这个过程按照XML层级的不同以及自己数据提取需求的不同,要针对性的写属性,外部架构都是一样的,关键是属性提取那里,目录都是写到需要提取的最深层级,然后用’../’来返回上一级(父亲节点,对这里是以树的形式存储临时数据的)。”EXEC
sp_xml_removedocument @hDoc”语句用来释放内存。

 

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

[, @xmltext = ]

idbigint文档节点的唯一 ID。

step3.把查询结果存到新表中,以下是代码示例

CREATE PROCEDURE dbo.SkyProcessXMLData (  @xml XML ) AS BEGIN  DECLARE
@docHandle INT  EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xml
 SELECT    *D  FROM   OPENXML(@docHandle, ‘/DataSet/tblEmp’, 2)  WITH (
    name VARCHAR(50),designation VARCHAR(50)   )  EXECUTE
sp_xml_removedocument @docHandle END

SELECT @xml

[, @xpath_namespaces =

根元素的 ID 值为 0。保留负 ID 值。

USE Badges
GO

EXEC sp_xml_preparedocument @Idoc OUTPUT, @xml

具体参数如下:

parentidbigint标识节点的父节点。此 ID
标识的父节点不一定是父元素。具体情况取决于此 ID
所标识节点的子节点的节点类型。例如,如果节点为文本节点,则其父节点可能是一个属性节点。

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT * into #temp FROM OPENXML (@Idoc,
‘/mediaData/Message/PhoneInfo’,2)

@hdoc:指向某内存区域的句柄,相关数据存放在这里。注意这是一个输出变量,当该进程运行后,该变量将包含指向XML文件内容在内存地址的句柄。由于你需要在随后使用此结果,因此要确保对其进行保存;

如果节点位于 XML 文档的顶层,则其 ParentID 为 NULL。

SELECT @XML = XMLData FROM XMLwithOpenXMLBadges

WITH (

相关文章