由于服务器数据库用的是SQL server2008R,同事向数据库中出入的文件只能选择xml。趁项目不着急，赶紧学习一下，怎么存储XML和解析XML。
- 存储XML
首先创建一个表，用来存储XML文件。
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY ,
XMLData XML ,
LoadedDateTime DATETIME
)向表中插入本地XML文件,使用到openrowset(bulk，...)函数
INSERT INTO XMLwithOpenXML (XMLDATA ,LoadedDateTime )
SELECT CONVERT (XML ,BulkColumn )AS BulkColumn ,GETDATE ()
FROM OPENROWSET (BULK 'd：\pic\test.xml' ,SINGLE_BLOB )AS X ;执行时提示
消息 4861，级别 16，状态 1，第 9 行
由于无法打开文件 "d：\pic\Testing.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',
reconfigure
exec sp_configure 'show advanced options',
reconfigure 启用外围应用配置器后，执行该insert 语句，成功。
select * from XMLwithOpenXML
- 解析XML
存储在数据类型 XML 列中的 XML 数据可以通过使用sp_xml_preparedocument存储过程以及OPENXML函数来处理。
首先通过指定 XML 数据来调用 sp_xml_preparedocument 存储过程，然后 XML 数据将输出它已准备好并存储在内部缓存中的 XML 数据的句柄。
然后使用 OPENXML 函数中 sp_xml_preparedocument 存储过程返回的句柄来打开 XML 数据并读取它。
注意：
- sp_xml_preparedocument存储过程将 XML 数据存储在 SQL Server 的内部缓存中，必须通过调用sp_xml_removedocument存储过程才能从内部缓存中释放存储的 XML 数据。
- 应该尽早调用sp_xml_removedocument存储过程，以便及时释放内部缓存。
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>SQL语句
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
--执行结果如下图
OPENXML()函数有三个参数：
- 个是 sp_xml_preparedocument 读取是的 OUTPUT 参数，在本示例中就是 @hdoc；
- 第二个是一个 XPath 表达式，用来获取指定位置的数据；
- 第三个是一个可选项，用来表示获取的方式，有 0,1,2,8 四种取值，如下
可选值:
- 0---> 默认为以特性为中心的映射。
- 1 --->使用以特性为中心的映射。在某些情况下，可以将它与 XML_ELEMENTS 组合使用。使用时首先应用以特性为中心的映射，然后对于所有仍未处理的列应用以元素为中心的映射。
- 2 --->使用以元素为中心的映射。在某些情况下，可以将它与 XML_ATTRIBUTES 组合使用。使用时先应用以特性为中心的映射，然后对于所有仍未处理的列应用以元素为中心的映射。
- 8---> 可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用（逻辑 OR）。在检索的上下文中，该标志指明不应将已消耗的数据复制到溢出属性 @mp:xmltext。
- FROM 后面的 WITH 也是可选的，用来指定获取哪些数据字段
- ------------------------网上找的，不知道啥意思，脸红-----------------------
如果我们想要导航回父级或祖父级别并从那里获取数据，我们需要使用 “../” 来读取父级数据，并使用 “../../” 来读取祖父级数据。
DECLARE @hdoc int;
DECLARE @doc varchar(1000);
SELECT @doc =convert(varchar(1000),XMLDATA) FROM XMLwithOpenXML WHERE ID=3
EXEC sp_xml_preparedocument @hdoc output,@doc;
select * from openxml (@hdoc,'Root/Customer/Order/OrderDetail',1)
with (
OrderID int,
ProductID VARCHAR(10),
Quantity INT,
DeptName varchar(10),
UserId int '../../@UserId'
)
EXEC sp_xml_removedocument @hdoc
我的实际工作中很少遇到解析xml文件的事情，如果有不足，欢迎指出。
