XML技术在SQL 2000中的应用
--OPENXML 是类似于表或视图的行集提供程序，用于提供内存中 XML 文档上的行集。
--OPENXML 通过提供 XML 文档内部表示法的行集视图，允许访问 XML 数据，就好象它是关系行集一样。行集中的记录可以存储在数据库表中。
-- attribute-centric 映射和 element-centric 映射
-- 1:attribute-centric
-- 2:element-centric
-- 3:attribute-centric+element-centric先进行属性匹配，匹配不上再进行元素匹配
--将 XML 拆分到表中
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
-- Use OPENXML to provide rowset consisting of customer data.
-- --INSERT Customers
-- SELECT *
-- FROM OPENXML(@hDoc, N'/ROOT/Customers')
-- WITH Customers--已经存在表Customers不要求列模式，此为给定的表名
-- where CustomerID='XYZAA'
-- Use OPENXML to provide rowset consisting of order data.
--INSERT Orders
--SELECT *
--FROM OPENXML(@hDoc, N'//Orders')
-- WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Customers/Orders') with (CustomerID1 nchar(5) '../@CustomerID', OrderDate1 datetime '@OrderDate')
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @hDoc
select * from Customers where CustomerID='XYZAA'
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML Auto
--
-- 下面的示例使用 sp_xml_preparedocument 创建 XML 图像的内部表示。然后对 XML 文档的内部表示法执行使用 OPENXML 行集提供程序的 SELECT 语句。
--
-- flag 值设置为 1，表示以特性为中心的映射。因此，XML 特性映射到行集中的列。指定为 /ROOT/Customers 的 rowpattern 标识要处理的 <Customers> 节点。
--
-- 没有指定可选的 colpattern（列模式），因为列名和 XML 特性名称匹配。
--
-- OPENXML 行集提供程序创建了一个双列行集（CustomerID 和 ContactName），SELECT 语句从该行集中检索必要的列（在本例中检索所有的列）。
DECLARE @idoc 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>
<CustomerID>11</CustomerID>
</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>
<CustomerID>12</CustomerID>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',2)
WITH (CustomerID varchar(10) ,
ContactName varchar(20))
--
-- 如果将 flags 设置为 2（表示以元素为中心的映射）并执行相同的 SELECT 语句，由于 <Customers> 元素没有任何子元素，
-- 则对于 XML 文档中两个 Customer 的 CustomerID 和 ContactName 的值都作为 NULL 返回。
-- 3 表示应使用 attribute-centric 映射和 element-centric
--
-- 尽管以元素为中心的映射由 flag 参数指定，但 ColPattern 中指定的映射重写该映射。
--B. 为列和 XML 特性之间的映射指定 ColPattern
declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
--指定列与XML文档元素间的映射关系，它会替代2参数指定的以元素为中心的映射关系
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
--
-- C. 获得边缘表格式的结果
--
-- 在下例中，在 OPENXML 语句中未指定 WITH 子句。因此，OPENXML 所生成的行集具有边缘表格式。SELECT 语句返回边缘表中的所有列。
--
-- 下例中的示例 XML 文档由 <Customer>、<Order> 和 <Order_0020_Details> 元素组成。
--
-- 首先调用 sp_xml_preparedocument 以获得文档句柄。此文档句柄传递到 OPENXML。
--
-- 在 OPENXML 语句中
--
-- rowpattern (/ROOT/Customer) 标识要处理的 <Customer> 节点。
--
--
-- 未提供 WITH 子句。因此 OPENXML 以边缘表格式返回行集。
-- 最后 SELECT 语句检索边缘表中的所有列。
declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT>
<Customers CustomerID="VINET" CompanyName="Company1" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" CompanyName="Company2" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
--With Customers
EXEC sp_xml_removedocument @idoc
-- 如果在 XML 文档中有多个文本节点，则含有 ColPattern (text()) 的 SELECT 语句只返回第一个文本节点，而不是返回所有节点。
DECLARE @h int
EXEC sp_xml_preparedocument @h OUTPUT,
N'
<root xmlns:a="urn:1">
<a:Elem abar="asdf">
T<a>a</a>U
</a:Elem>
</root>',
'<ns xmlns:b="urn:1" />'
SELECT * FROM openxml(@h, '/root/b:Elem')
WITH (Col1 varchar(20) 'text()')
--
-- I. 从多值特性中检索单值
-- XML 文档会含有多值特性。例如 IDREFS 可为多值特性。在 XML 文档内，多值特性值被指定为由用空格隔开的值组成的字符串。在下列 XML 文档内，<Student> 元素的 attends 特性与 <Class> 的 attendedBy 特性都是多值特性。从多值 XML 属性中检索单值并将每个值存储到数据库中的不同行中，这要求额外的工作。下例显示了此过程。
--
-- 该 XML 示例文档由以下元素组成：
--
-- <Student>
-- 包括 id（学生 ID）、name 和 attends 特性。该 attends 特性是多值特性。
--
-- <Class>
-- 包括 id（班级 ID）、name 和 attendedBy 特性。该 attendedBy 特性是多值特性。
--
-- <Student> 内的 attends 特性和 <Class> 内的 attendedBy 特性表示 Student 表与 Class 表之间的一种 m:n 关系。一个学生可在很多班上课，而一个班也可有很多学生。
--
-- 假设希望拆分该文档，并将它以下列形式保存到数据库中：
--
-- 将 <Student> 数据保存到 Students 表中。
--
--
-- 将 <Class> 数据保存到 Courses 表中。
--
--
-- 将 m:n 关系数据（在 Student 与 Class之间）保存到 CourseAttendence 表中。析取这些值需要做额外的工作。若要检索该信息并将其存储到表中，请使用下列存储过程：
-- Insert_Idrefs_Values
-- 将课程 ID 和学生 ID 的值插入 CourseAttendence 表中。
--
-- Extract_idrefs_values
-- 从每个 <Course> 元素中析取单个学生 ID。用边缘表检索这些值。
--
-- 以下是具体步骤：
--
-- 创建下表：
DROP TABLE CourseAttendance
DROP TABLE Students
DROP TABLE Courses
GO
CREATE TABLE Students(
id varchar(5) primary key,
name varchar(30)
)
GO
CREATE TABLE Courses(
id varchar(5) primary key,
name varchar(30),
taughtBy varchar(5)
)
GO
CREATE TABLE CourseAttendance(
id varchar(5) references Courses(id),
attendedBy varchar(5) references Students(id),
constraint CourseAttendance_PK primary key (id, attendedBy)
)
go
创建下列存储过程：
DROP PROCEDURE f_idrefs
GO
CREATE PROCEDURE f_idrefs
@t varchar(500),
@idtab varchar(50),
@id varchar(5)
AS
DECLARE @sp int
DECLARE @att varchar(5)
SET @sp = 0
WHILE (LEN(@t) > 0)
BEGIN
SET @sp = CHARINDEX(' ', @t+ ' ')
SET @att = LEFT(@t, @sp-1)
EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')')
SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp)
END
Go
DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
@xmldoc int,
@xpath varchar(100),
@from varchar(50),
@to varchar(50),
@idtable varchar(100)
AS
DECLARE @t varchar(500)
DECLARE @id varchar(5)
/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath)
DECLARE fillidrefs_cursor CURSOR FOR
SELECT CAST(iv.text AS nvarchar(200)) AS id,
CAST(av.text AS nvarchar(4000)) AS refs
FROM #TempEdge c, #TempEdge i,
#TempEdge iv, #TempEdge a, #TempEdge av
WHERE c.id = i.parentid
AND UPPER(i.localname) = UPPER(@from)
AND i.id = iv.parentid
AND c.id = a.parentid
AND UPPER(a.localname) = UPPER(@to)
AND a.id = av.parentid
OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
execute f_idrefs @t, @idtable, @id
END
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END
CLOSE fillidrefs_cursor
DEALLOCATE fillidrefs_cursor
Go
--这是已拆分的示例文档，已将数据存储到前面提到的表中。
DECLARE @h int
EXECUTE sp_xml_preparedocument @h OUTPUT, '
<Data>
<Student id = "s1" name = "Student1" attends = "c1 c3 c6" />
<Student id = "s2" name = "Student2" attends = "c2 c4" />
<Student id = "s3" name = "Student3" attends = "c2 c4 c6" />
<Student id = "s4" name = "Student4" attends = "c1 c3 c5" />
<Student id = "s5" name = "Student5" attends = "c1 c3 c5 c6" />
<Student id = "s6" name = "Student6" />
<Class id = "c1" name = "Intro to Programming"
attendedBy = "s1 s4 s5" />
<Class id = "c2" name = "Databases"
attendedBy = "s2 s3" />
<Class id = "c3" name = "Operating Systems"
attendedBy = "s1 s4 s5" />
<Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
<Class id = "c5" name = "Algorithms and Graphs"
attendedBy = "s4 s5"/>
<Class id = "c6" name = "Power and Pragmatism"
attendedBy = "s1 s3 s5" />
</Data>'
INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students
INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance'
SELECT * FROM Students
SELECT * FROM Courses
SELECT * FROM CourseAttendance
EXECUTE sp_xml_removedocument @h
/*
--
下表描述边缘表的结构。
列名 数据类型 描述
id bigint 是文档节点的唯一 ID。
根元素的 ID 值为 0。保留负 ID 值。
parentid bigint 标识节点的父节点。此 ID 所标识的父节点不一定是父元素，而是取决于此 ID 所标识节点的子节点的 NodeType。例如，如果节点是文本节点，则其父节点可能是特性节点。
如果节点位于 XML 文档的顶层，则其 ParentID 为 NULL。
nodetype int 标识节点类型。是对应于 XML DOM 节点类型编号的整数（有关节点信息，请参见 DOM）。
三种节点类型是：
1 = 元素节点
2 = 特性节点
3 = 文本节点
localname nvarchar 给出元素或特性的本地名称。如果 DOM 对象没有名称则为 NULL。
prefix nvarchar 是节点名称的命名空间前缀。
namespaceuri nvarchar 是节点的命名空间 URI。如果值是 NULL，则命名空间不存在。
datatype nvarchar 是元素或特性行的实际数据类型，否则是 NULL。从内嵌 DTD 中或从内嵌架构中推断数据类型。
prev bigint 是前一个兄弟元素的 XML ID。如果前面没有兄弟元素则为 NULL。
text ntext 包含文本格式的特性值或元素内容（如果边缘表项不需要值则为 NULL）。
--两个重要的存储过程
sp_xml_preparedocument
读取作为输入提供的可扩展标记语言 (XML) 文本，然后使用 MSXML 语法分析器 (Msxml2.dll) 对其进行分析，
并提供分析后的文档供您使用。分析后的文档是 XML 文档中各节点（元素、特性、文本、注释等）的树型表示法。
sp_xml_preparedocument 返回一个句柄，可用于访问 XML 文档的新创建的内部表示方式。
该句柄在连接到 Microsoft? SQL Server? 2000 期间保持有效，直到重置连接或执行 sp_xml_removedocument 使句柄无效为止。
说明 分析过的文档存储在 SQL Server 2000 的内部高速缓存中。MSXML 语法分析器使用 SQL Server 可用总内存的八分之一。
若要避免内存不足，请运行 sp_xml_removedocument 以释放内存。
语法
sp_xml_preparedocument hdoc OUTPUT
[, xmltext]
[, xpath_namespaces]
参数
hdoc
是新创建的文档的句柄。hdoc 的数据类型为 integer。
[xmltext]
是原 XML 文档。MSXML 语法分析器分析该 XML 文档。xmltext 是 text 类型（char、nchar、varchar、nvarchar、text 或 ntext）的参数。
默认值是 NULL，在这种情况下，将创建空 XML 文档的内部表示法。
[xpath_namespaces]
指定 OPENXML 的行和列 XPath 表达式中所使用的命名空间声明。默认值是 <root xmlns:mp="urn:schemas-microsoft-com:xml-metaprop">。
xpath_namespaces 通过符合语法规则的 XML 文档的方式，为在 OPENXML 的 Xpath 表达式中使用的前缀提供命名空间 URI。
xpath_namespaces 声明前缀必须用于引用命名空间 urn:schemas-microsoft-com:xml-metaprop，该命名空间提供有关分析后的 XML 元素的元数据。
尽管可以使用此方法为元属性命名空间重新定义命名空间前缀，但此命名空间不会丢失。此前缀 mp 对 urn:schemas-microsoft-com:xml-metaprop
仍有效，即使 xpath_namespaces 不包含此类声明。xpath_namespaces 是 text 类型（char、nchar、varchar、nvarchar、text 或 ntext）的参数。
返回代码值
0（成功）或 >0（失败）
权限
执行权限默认授予 public 角色。
sp_xml_removedocument
删除文档句柄指定的 XML 文档的内部表示方式并使该文档句柄无效。
说明 分析过的文档保存在 Microsoft? SQL Server? 2000 的内部高速缓存中。MSXML 语法分析器使用 SQL Server 可用总内存的八分之一。
若要避免内存不足，请运行 sp_xml_removedocument 以释放内存。
语法
sp_xml_removedocument hdoc
参数
hdoc
是新创建的文档的句柄。无效句柄返回错误。hdoc 的数据类型为 integer。
返回代码值
0（成功）或 >0（失败）
权限
执行权限默认授予 public 角色。
*/
浙公网安备 33010602011771号