管理XML数据

实验四 管理XML数据

一、目标

完成这个实验后，你将能够： 1、使用XML结构 2、检索XML数据 二、实验内容

实验开始前，请执行脚本instnwnd.sql，安装Northwind数据库。本实验均在Northwind数据库中完成。 三、实验报告内容

练习1：使用XML结构

1、创建一个含有XML类型字段的数据表。

以示例数据库Northwind为例，在该数据库中创建一个名为xml_Categories的数据表，该数据表中包含两个字段，如下表所示。 字段名 类 型 CategoryID Int，标识，且为主键 CategoryInfo XML 代码： CREATE TABLE xml_Categories (

CategoryID INT IDENTITY PRIMARY KEY, CategoryInfo XML );

2、向该数据表输入一条记录，CategoryInfo字段值如下：

BeveragesSoft drinks, coffees, teas, beers, and ales 代码：

INSERT INTO xml_Categories(CategoryInfo)

VALUES('BeveragesSoft drinks, coffees, teas, beers, and ales'); 3、查询表中信息 代码：

SELECT * FROM xml_Categories 结果：

Beverages Soft drinks, coffees, teas, beers, and ales

4、创建XML数据类型变量，定义XML变量@CategoryInfo，将其值设置为

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

代码：

DECLARE @CategoryInfo XML

SET @CategoryInfo=' Condiments

Sweet and savory sauces, relishes, spreads, and seasonings '

SELECT @CategoryInfo 结果：

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

5、将@CategoryInfo值插入xml_Categories表中。 代码：

DECLARE @CategoryInfo XML

SET @CategoryInfo=' Condiments

Sweet and savory sauces, relishes, spreads, and seasonings '

INSERT xml_Categories(CategoryInfo)

VALUES(@CategoryInfo)

SELECT * FROM xml_Categories 结果：

练习2：创建架构集合

1、在数据库Northwind中创建一个名为testXMLSchema的XML架构集合。要求如下：

代码：

CREATE XML SCHEMA COLLECTION testXMLSchema AS

'

'

2、创建一个名为xsd_Categories的数据表，包含一个Categories字段，为XML类型，并将架构集合testXMLSchema与其关联。 代码：

CREATE TABLE xsd_Categories (

Categories XML(testXMLSchema)

)

3、向数据表xsc_Categories中插入一条记录，值为

1

candy

chocolate,panocha 代码：

INSERT xsd_Categories VALUES('

1

candy

chocolate,panocha ')

Select * from xsd_Categories 结果：

4、将上面的Transact SQL脚本稍作改动，将

1 更改为

abcd 结果如何？为什么？

结果为：消息6926，级别16，状态1，第1 行

XML 验证: 简单类型值'abcd' 无效。位置:

/*:CategoryInfo[1]/*:CategoryId[1]

原因是 testXMLSchema中定义的CategoryId的类型是integer

练习3：检索XML数据

1、使用FOR XML RAW模式，从Products表中输出前5条记录，结果如下。

代码：

SELCT TOP 5 ProducTID,PboductName,Supp,ierID,CAtegoryID frgm Products

ORDER BY ProductID FOR XML RAW

2、使用FOR XML RAW模式，从Products表中输出前5条记录，结果如下。

1

Chai 12 1

2

Chang 12 1

3

Aniseed Syrup 12 2

4

Chef Anton's Cajun Seasoning 2 2

5

Chef Anton's Gumbo Mix 2 2 代码：

SELECT TOP 5 ProductID,ProductName,SupplierID,CategoryID from Products

ORDER BY ProductID

FOR XML RAW('Product'),ELEMENTS

3、使用FOR XML AUTO模式，从Categories和Products表中输出CategoryID为1或2的记录，包括每种类别的编号、名称、描述信息以及该种类别的产品信息，结果如下。

drinks, coffees, teas, beers, and ales\>

代码： SELECT

Category.CategoryID,CategoryName,[Description],ProductID,ProductName FROM Categories Category INNER JOIN Products Product

ON Category.CategoryID=Product.CategoryID WHERE Category.CategoryID IN(1,2) ORDER BY Category.CategoryID FOR XML AUTO

4、使用FOR XML AUTO模式，从Categories和Products表中输出CategoryID为1或2的记录，包括每种类别的编号、名称、描述信息以及该种类别的产品信息，结果如下。

1

Beverages Soft drinks, coffees, teas, beers, and ales

1

Chai

2

Chang

24

Guaraná Fantástica

34

Sasquatch Ale

35

Steeleye Stout

38

C?te de Blaye

39

Chartreuse verte

43

Ipoh Coffee

67

Laughing Lumberjack Lager

70

Outback Lager

75

Rh?nbr?u Klosterbier

76

Lakkalik??ri

2

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

3

Aniseed Syrup

4

Chef Anton's Cajun Seasoning

5

Chef Anton's Gumbo Mix

6

Grandma's Boysenberry Spread

8

Northwoods Cranberry Sauce

15

Genen Shouyu

44

Gula Malacca

61

Sirop d'érable

63

Vegie-spread

65

Louisiana Fiery Hot Pepper Sauce

66

Louisiana Hot Spiced Okra

77

Original Frankfurter grüne So?e 代码: SELECT

Category.CategoryID,CategoryName,[Description],ProductID,ProductName FROM Categories Category INNER JOIN Products Product

ON Category.CategoryID=Product.CategoryID WHERE Category.CategoryID IN(1,2) ORDER BY Category.CategoryID FOR XML AUTO,ELEMENTS 5、使用FOR XML PATH模式，检索每个订单及其客户的相关信息，部分结果如下，其中Address元素由Address，City及PostalCode构成。

Vins et alcools Chevalier Paul Henriot

59 rue de l'Abbaye Reims 51100

Toms Spezialit?ten Karin Josephs

Luisenstr. 48 Münster 44087

代码：

SELECT [Order].OrderID 'Order/@OrderID', OrderDate 'Order/@OrderDate', RequiredDate'Order/@Order',

Customer.CustomerID 'Order/Customer/@CustomerID', CompanyName 'Order/Customer/CompanyName', ContactName 'Order/Customer/ContactName', [Address] 'Order/Customer/Address/data()', City 'Order/Customer/Address/data()',

PostalCode 'Order/Customer/Address/data()' FROM Orders [Order]

INNER JOIN Customers Customer

ON [Order].CustomerID=Customer.CustomerID ORDER BY [Order].OrderID FOR XML PATH('')

6、选做：使用FOR XML PATH模式，检索10248号订单的相关信息，结果如下：

Vins et alcools Chevalier Paul Henriot

59 rue de l'Abbaye Reims 51100

Queso Cabrales 21.0000 12

0.0000000e+000

Vins et alcools Chevalier Paul Henriot

59 rue de l'Abbaye Reims 51100

Singaporean Hokkien Fried Mee 14.0000

10

0.0000000e+000

Vins et alcools Chevalier Paul Henriot

59 rue de l'Abbaye Reims 51100

Mozzarella di Giovanni 34.8000 5

0.0000000e+000 代码：

SELECT [Order].OrderID 'Order/@OrderID', OrderDate 'Order/@OrderDate', RequiredDate'Order/@Order',

Customer.CustomerID 'Order/Customer/@CustomerID', CompanyName 'Order/Customer/CompanyName', ContactName 'Order/Customer/CojtactNaee', [Address] 'Order/Customer/Address/data()', City 'Order/Customer/Address/data()',

Postalode 'Order/Customer/Address/data()',

[Order Details].ProductID 'Order/ItemList/Product@Product D',

ProductName 'Order/ITemList/Product/ProductName', Quantity 'Order/ItemList/Product/Quantity',

Discount 'Ordep/ItemList/Product/Discount'

FROM Orders [Order],Customers Customer,[Order Details],Products WHERE [Order].CustomerID=Customer.CustomerID AND [Order Details].OrderID=[Order].OrderID AND [Order Details].ProductID=Products.ProductID And [Order].OrderID=10248 FOR XML PATH('')

7、使用FOR XML PATH模式，检索每个订单、产品及其客户的相关信息，输出前两条记录，结果如下。

Vins et alcools Chevalier Paul Henriot

59 rue de l'Abbaye Reims 51100

Toms Spezialit?ten Karin Josephs

Luisenstr. 48 Münster 44087

代码：

SELECT TOP 2 Orders.OrderID 'Order/@OrderID', OrderDate 'Order/@OrderDate',

RequiredDate'Order/@RequiredDate',

Customer.CustomerID 'Order/Customer/@CustomerID', CompanyName 'Order/Customer/CompanyName', ContactName 'Order/Customer/ContactName', [Address] 'Order/Customer/Address/data()', City 'Order/Customer/Address/data()',

PostalCode 'Order/Customer/Address/data()', (

SELECT Products.ProductID ,

ProductName ,

Products.UnitPrice , Quantity

FROM Products,[Order Details] L

WHERE Products.ProductID=L.ProductID AND L.OrderID=Orders.OrderID

FOR XML RAW('Product'),TYPE ) 'Order/Products'

FROM Orders, Customers Customer

WHERE Orders.CustomerID=Customer.CustomerID ORDER BY Orders.OrderID

FOR XML PATH(''),ROOT('Orders')

8、使用FOR XML EXPLICIT模式，检索每个订单的相关信息，部分结果如下。

…… 代码：

SELECT DISTINCT 1 AS TAG, NULL AS PARENT,

Orders.OrderID AS [Order!1!OrderID],

Customers.CustomerID AS [Order!1!CustomerID], OrderDate AS [Order!1!OrderDate], NULL AS [Product!2!ProductID], NULL AS [Product!2!UnitPrice], NULL AS [Product!2!Quantity]

FROM Orders,Customers,[Order Details],Products

WHERE Orders.OrderID=[Order Details].OrderID AND [Order Details].ProductID=Products.ProductID AND Orders.CustomerID=Customers.CustomerID UNION ALL

SELECT 2 AS TAG, 1 AS PARENT,

Orders.OrderID,

Customers.CustomerID, OrderDate,

Products.ProductID,

[Order Details].UnitPrice, Quantity

FROM Orders,Customers,[Order Details],Products

WHERE Orders.OrderID=[Order Details].OrderID AND [Order Details].ProductID=Products.ProductID AND Orders.CustomerID=Customers.CustomerID

ORDER BY [Order!1!OrderID],[Product!2!ProductID] FOR XML EXPLICIT,ROOT('Orders')

9、在SSMS中执行如下脚本，结果如何？ DECLARE @tmpXML AS XML SET @tmpXML = '

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses '

SELECT @tmpXML.query('

for $var in /Categories/CategoryInfo return($var)') AS test GO

结果：

Beverages Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses

10、 若需输出结果如下，则上述脚本如何更改？

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings 更改代码如下：

SELECT @tmpXML.query('

for $var in /Categories/CategoryInfo[2] return $var') AS test

练习4：修改XML数据

1、定义xml变量，并对其赋值，如下所示 DECLARE @tmpXML AS XML SET @tmpXML = '

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Dairy Products Cheeses

2、编写脚本，插入节点，结果如下

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses 代码;

SET @tmpXML.modify(

'insert

Confections

Desserts, candies, and sweet breads

after (/Categories/CategoryInfo[2])[1]') 3、编写脚本，插入节点，结果如下

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products

Cheeses 代码：

SET @tmpXML.modify(

'insert Beverages as first

into (/Categories/CategoryInfo[1])[1]') 4、使用如下脚本创建表T，并输入数据。 DECLARE @tmpXML AS XML SET @tmpXML = '

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Dairy Products Cheeses '

CREATE TABLE T (i int, x xml); INSERT INTO T VALUES(1,@tmpXML)

5、修改表T中xml数据，增加节点，结果同2。 代码： UPDATE T

SET x.modify('

insert

Confections

Desserts, candies, and sweet breads

after (/Categories/CategoryInfo[2])[1] ')

6、定义如下变量

DECLARE @tmpXML AS XML SET @tmpXML = '

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses '

7、编写脚本，删除节点，结果如下

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Dairy Products Cheeses 代码:

SET @tmpXML.modify('

delete /Categories/CategoryInfo[3] ')

8、编写脚本，修改表T中xml数据，删除第5步中增加的节点。 代码： UPDATE T

SET x.modify('

delete /Categories/CategoryInfo[3] ')

9、将第6步中定义的变量CategoryID =\改为CategoryID =\。 代码：

SET @tmpXML.modify('

replace value of(/Categories/CategoryInfo[4]/@CategoryID)[1] with 14 ')

练习5：将XML数据转换为关系型数据

1、 有如下xml文档

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses

2、 使用OPENXML的隐式映射，将其转换为如下关系型数据

代码：

DECLARE @doc VARCHAR(1000) SET @doc='

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses ' DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,@doc SELECT * FROM

OPENXML(@idoc,'/Categories/CategoryInfo',2) WITH

(CategoryName VARCHAR(100), [Description] VARCHAR(100) )

3、 使用OPENXML的显式映射，将其转换为如下关系型数据。

代码：

DECLARE @doc VARCHAR(1000) SET @doc='

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and

seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses ' DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,@doc SELECT * FROM

OPENXML(@idoc,'/Categories/CategoryInfo',2) WITH (

CategoryID int '@CategoryID',

CategoryName VARCHAR(100) 'CategoryName', [Description] VARCHAR(100) 'Description' )

seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses ' DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,@doc SELECT * FROM

OPENXML(@idoc,'/Categories/CategoryInfo',2) WITH (

CategoryID int '@CategoryID',

CategoryName VARCHAR(100) 'CategoryName', [Description] VARCHAR(100) 'Description' )

本文来源：https://www.bwwdw.com/article/bmh3.html