SQL Server如何导入上传的XML OPENXML函数的使用

在SQL Server中处理外部上传的XML数据时，OPENXML函数是官方提供的行集提供程序，能够将XML文档转换为关系型行集，方便后续的数据查询和入库操作。它需要和sp_xml_preparedocument、sp_xml_removedocument两个系统存储过程配合使用，才能完成完整的XML解析流程。

OPENXML相关核心组件说明

使用OPENXML处理XML数据前，需要先了解三个核心部分的作用：

• sp_xml_preparedocument：系统存储过程，用于将XML文档解析为内部表示的XML文档，并返回对应的文档句柄，后续OPENXML需要通过这个句柄访问XML内容。
• OPENXML：行集函数，接收文档句柄、XML节点的XPath路径、映射标志三个参数，返回指定XML节点的解析结果行集。
• sp_xml_removedocument：系统存储过程，用于释放sp_xml_preparedocument创建的XML文档句柄，避免内存泄漏。

OPENXML函数语法说明

OPENXML的基本语法格式如下：

OPENXML ( xml_document_handle , rowpattern [, flags ] )
WITH ( 
    column_name column_type [ column_pattern | meta_property ] 
    [,...n ] 
)

参数说明：

• xml_document_handle：sp_xml_preparedocument返回的文档句柄。
• rowpattern：XML文档中需要解析的节点的XPath路径，函数会为每个匹配该路径的节点生成一行数据。
• flags：可选参数，指定XML数据和关系行集之间的映射方式，常用值有1（以属性为中心映射）、2（以元素为中心映射）、3（同时支持属性和元素映射）。
• WITH子句：指定返回行集的列结构，column_name是列名，column_type是列的数据类型，column_pattern是可选的XML节点路径，用于指定该列对应XML中的具体属性或子元素。

完整使用示例：导入XML用户数据

假设我们上传的XML内容包含多个用户的信息，需要将数据解析后插入到用户表中，具体步骤如下：

1. 准备测试表和XML数据

首先创建存储用户数据的目标表：

-- 创建用户表
CREATE TABLE User_Info (
    UserId INT,
    UserName NVARCHAR(50),
    UserAge INT,
    UserEmail NVARCHAR(100)
)

待导入的XML数据内容如下：

<Users>
    <User UserId="1001" UserName="张三" UserAge="25" UserEmail="zhangsan@ipipp.com" />
    <User UserId="1002" UserName="李四" UserAge="28" UserEmail="lisi@ipipp.com" />
    <User UserId="1003" UserName="王五" UserAge="22" UserEmail="wangwu@ipipp.com" />
</Users>

2. 使用OPENXML解析并导入数据

完整的导入脚本如下：

-- 声明变量存储XML文档句柄和XML内容
DECLARE @XmlDoc NVARCHAR(MAX)
DECLARE @XmlHandle INT

-- 赋值XML内容，实际场景中可以从上传的文件或者参数中获取
SET @XmlDoc = N'<Users>
    <User UserId="1001" UserName="张三" UserAge="25" UserEmail="zhangsan@ipipp.com" />
    <User UserId="1002" UserName="李四" UserAge="28" UserEmail="lisi@ipipp.com" />
    <User UserId="1003" UserName="王五" UserAge="22" UserEmail="wangwu@ipipp.com" />
</Users>'

-- 解析XML文档，获取句柄
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @XmlDoc

-- 使用OPENXML解析XML并插入到用户表，这里flags用1表示以属性为中心映射
INSERT INTO User_Info (UserId, UserName, UserAge, UserEmail)
SELECT UserId, UserName, UserAge, UserEmail
FROM OPENXML (@XmlHandle, '/Users/User', 1)
WITH (
    UserId INT '@UserId',
    UserName NVARCHAR(50) '@UserName',
    UserAge INT '@UserAge',
    UserEmail NVARCHAR(100) '@UserEmail'
)

-- 释放XML文档句柄，避免内存占用
EXEC sp_xml_removedocument @XmlHandle

-- 查询插入结果验证
SELECT * FROM User_Info

上述脚本中，OPENXML的rowpattern指定为/Users/User，表示匹配所有User节点，flags为1表示XML数据以属性形式存储，WITH子句中用@属性名的方式指定每个列对应User节点的属性。

3. 处理元素形式存储的XML数据

如果XML数据是以子元素形式存储的，只需要调整flags和WITH子句的映射即可，例如XML内容如下：

<Users>
    <User>
        <UserId>1004</UserId>
        <UserName>赵六</UserName>
        <UserAge>30</UserAge>
        <UserEmail>zhaoliu@ipipp.com</UserEmail>
    </User>
</Users>

对应的解析脚本调整为：

DECLARE @XmlDoc NVARCHAR(MAX)
DECLARE @XmlHandle INT

SET @XmlDoc = N'<Users>
    <User>
        <UserId>1004</UserId>
        <UserName>赵六</UserName>
        <UserAge>30</UserAge>
        <UserEmail>zhaoliu@ipipp.com</UserEmail>
    </User>
</Users>'

EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @XmlDoc

-- flags改为2表示以元素为中心映射
INSERT INTO User_Info (UserId, UserName, UserAge, UserEmail)
SELECT UserId, UserName, UserAge, UserEmail
FROM OPENXML (@XmlHandle, '/Users/User', 2)
WITH (
    UserId INT 'UserId',
    UserName NVARCHAR(50) 'UserName',
    UserAge INT 'UserAge',
    UserEmail NVARCHAR(100) 'UserEmail'
)

EXEC sp_xml_removedocument @XmlHandle

使用注意事项

• 每次调用sp_xml_preparedocument之后，必须调用sp_xml_removedocument释放句柄，否则会导致SQL Server内存占用持续升高。
• OPENXML的flags参数需要根据XML数据的存储形式选择，属性存储用1，元素存储用2，混合存储用3，选择错误会导致解析不到数据。
• 如果XML文档较大，OPENXML的性能会有所下降，此时可以考虑使用SQL Server 2005之后引入的XQuery方法（如nodes()、value()），但OPENXML在兼容旧版本或者简单XML解析场景下仍然有较高的实用性。
• XML中的特殊字符如<、>、&需要提前进行转义，否则sp_xml_preparedocument会解析失败。

SQL_ServerOPENXMLXML导入sp_xml_preparedocumentsp_xml_removedocument修改时间：2026-07-20 18:42:38