菜单

mssqlserver中读取xml文件技巧

2020年4月23日 - 数据网络

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

然而,要使用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/

第一个是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左右的表也没有出现这个问题,暂时先不解决了。

代码如下复制代码

数据类型

step2.Process XML data using OPENXML function

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”语句用来释放内存。

[, @xmltext = ]

idbigint文档节点的唯一 ID。

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

[, @xpath_namespaces =

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

USE Badges
GO

具体参数如下:

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

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

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

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

SELECT @XML = XMLData FROM XMLwithOpenXMLBadges

@xmltext:实际上你所希望处理的XML文本;

节点类型int标识节点类型,是对应于 XML 对象模型 (DOM)
节点类型编号的一个整数。

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

@xml_namespaces:为了正常操作你的XML数据所需要的任何名字空间索引。注意在这里出现的任何URL都需要用尖括号括起来;

下列值是可以显示在此列中以指明节点类型的值:

CREATE TABLE Badges
(
UserId varchar(50),
Name varchar(100),
Date datetime
);

假设所传递的这些参数都有效,并且XML文档存在,那么你的XML数据就会被存放到内存中去。现在你就可以调用sp_xml_preparedocument,传递存放有XML文件的变量,然后执行OPENXML。语法如下:

1 = 元素节点

INSERT INTO Badges
SELECT UserId,Name,Date
FROM OPENXML(@hDoc,’badges/row’)
WITH
(
UserId varchar ‘@UserId’,
Name varchar ‘@Name’,
Date datetime ‘@Date’
)

代码如下复制代码

2 = 属性节点

EXEC sp_xml_removedocument @hDoc
GO

OPENXML(idocint [in],rowpatternnvarchar[in],[flagsbyte[in]])

3 = 文本节点

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图