sqlserver 解析导入xml

XML文件内容=XMLDATA

<Root>
  <Customer UserId="505" UserCode="90099" UserName="22">
    <Order orderid="1" sum="199">
      <OrderDetail OrderID="543" ProductID="90099" Quantity="12" DeptId="25" DeptNo="25" DeptName="JIM" />
      <OrderDetail OrderID="544" ProductID="90100" Quantity="5" DeptId="25" DeptNo="25" DeptName="TOM" />
    </Order>
  </Customer>
  <Customer UserId="505" UserCode="90099" UserName="22">
    <Order orderid="2" sum="299">
      <OrderDetail OrderID="545" ProductID="90101" Quantity="2" DeptId="25" DeptNo="25" DeptName="lily" />
      <OrderDetail OrderID="546" ProductID="90102" Quantity="6" DeptId="25" DeptNo="25" DeptName="lilei" />
    </Order>
  </Customer>
</Root>

首先创建一个表，用来存储XML

CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY ,
XMLData XML ,
LoadedDateTime DATETIME
 )

向表中插入本地XML文件

INSERT INTO XMLwithOpenXML (XMLDATA ,LoadedDateTime )
SELECT CONVERT (XML ,BulkColumn )AS BulkColumn ,GETDATE ()
FROM OPENROWSET (BULK 'C:\Users\Administrator\Desktop\xml\TaskConfig.xml' ,SINGLE_BLOB )AS X ;

执行时提示

由于无法打开文件 "C:\Users\Administrator\Desktop\xml\TaskConfig.xml"，无法进行大容量加载。操作系统错误代码为 3(系统找不到指定的路径。)。

--开启外围应用配置器（OpenRowset 和 OpenDataSource）
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

执行插入语句

--关闭外围应用配置器
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 

解析xml

DECLARE @hdoc int;  --执行存储过程sp_xml_preparedocument 需要返回的结果，存储在缓存中。
DECLARE @doc varchar(1000);  --将xml文件数据赋值给@doc
SELECT @doc =convert(varchar(1000),XMLDATA) FROM  XMLwithOpenXML WHERE ID=3

--执行存储过程，解析XML文件
EXEC sp_xml_preparedocument @hdoc output,@doc;

--使用OPENXML()格式化xml文件。
select * from openxml (@hdoc,'Root/Customer/Order/OrderDetail',1)
with (OrderID int,ProductID VARCHAR(10),Quantity INT,DeptName varchar(10))
--执行存储过程 释放缓存
EXEC sp_xml_removedocument @hdoc 

--放在文本节点
DECLARE @xml XML
SET @xml=(SELECT XMLDATA FROM XMLwithOpenXML);
DECLARE @str int
EXEC sp_xml_preparedocument @str OUTPUT, @xml
--SELECT * FROM OPENXML(@str,'/Data/Lot',2)
SELECT * FROM OPENXML(@str,'/Root/TaskList/Task',2)
WITH (TaskID NVARCHAR(50),CronCode NVARCHAR(50),Meno NVARCHAR(300),TaskUrl NVARCHAR(200) ,TaskToken NVARCHAR(100),Cron NVARCHAR(100),IsFast bit,IsDel bit,[State] bit)
EXEC sys.sp_xml_removedocument @str