管理XML数据.doc

实验四 管理XML数据一、目标 完成这个实验后，你将能够：1、使用XML结构2、检索XML数据二、实验内容实验开始前，请执行脚本instnwnd.sql，安装Northwind数据库。本实验均在Northwind数据库中完成。三、实验报告内容练习1：使用XML结构1、 创建一个含有XML类型字段的数据表。以示例数据库Northwind为例，在该数据库中创建一个名为xml_Categories的数据表，该数据表中包含两个字段，如下表所示。字段名类 型CategoryIDInt，标识，且为主键CategoryInfoXML代码：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 ales4、 创建XML数据类型变量，定义XML变量CategoryInfo，将其值设置为 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 代码：DECLARE CategoryInfo XMLSET 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 XMLSET 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 testXMLSchemaAS 2、 创建一个名为xsd_Categories的数据表，包含一个Categories字段，为XML类型，并将架构集合testXMLSchema与其关联。代码：CREATE TABLE xsd_Categories(Categories XML(testXMLSchema)3、 向数据表xsc_Categories中插入一条记录，值为1candy chocolate,panocha代码：INSERT xsd_CategoriesVALUES(1candy chocolate,panocha)Select * from xsd_Categories结果：4、 将上面的Transact SQL脚本稍作改动，将1更改为abcd结果如何？为什么？结果为：消息6926，级别16，状态1，第1 行XML 验证: 简单类型值abcd 无效。位置: /*:CategoryInfo1/*:CategoryId1原因是 testXMLSchema中定义的CategoryId的类型是integer练习3：检索XML数据1、 使用FOR XML RAW模式，从Products表中输出前5条记录，结果如下。代码：SELCT TOP 5 ProducTID,PboductName,Supp,ierID,CAtegoryIDfrgm ProductsORDER BY ProductIDFOR XML RAW2、 使用FOR XML RAW模式，从Products表中输出前5条记录，结果如下。 1 Chai 12 1 2 Chang 12 1 3 Aniseed Syrup 12 2 4 Chef Antons Cajun Seasoning 2 2 5 Chef Antons Gumbo Mix 2 2代码：SELECT TOP 5 ProductID,ProductName,SupplierID,CategoryIDfrom ProductsORDER BY ProductIDFOR XML RAW(Product),ELEMENTS3、 使用FOR XML AUTO模式，从Categories和Products表中输出CategoryID为1或2的记录，包括每种类别的编号、名称、描述信息以及该种类别的产品信息，结果如下。 代码：SELECT Category.CategoryID,CategoryName,Description,ProductID,ProductNameFROM Categories CategoryINNER JOIN Products ProductON Category.CategoryID=Product.CategoryIDWHERE Category.CategoryID IN(1,2)ORDER BY Category.CategoryIDFOR XML AUTO4、 使用FOR XML AUTO模式，从Categories和Products表中输出CategoryID为1或2的记录，包括每种类别的编号、名称、描述信息以及该种类别的产品信息，结果如下。 1 Beverages Soft drinks, coffees, teas, beers, and ales 1 Chai 2 Chang 24 Guaran Fantstica 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 Antons Cajun Seasoning 5 Chef Antons Gumbo Mix 6 Grandmas Boysenberry Spread 8 Northwoods Cranberry Sauce 15 Genen Shouyu 44 Gula Malacca 61 Sirop drable 63 Vegie-spread 65 Louisiana Fiery Hot Pepper Sauce 66 Louisiana Hot Spiced Okra 77 Original Frankfurter grne So?e 代码:SELECT Category.CategoryID,CategoryName,Description,ProductID,ProductNameFROM Categories CategoryINNER JOIN Products ProductON Category.CategoryID=Product.CategoryIDWHERE Category.CategoryID IN(1,2)ORDER BY Category.CategoryIDFOR XML AUTO,ELEMENTS5、 使用FOR XML PATH模式，检索每个订单及其客户的相关信息，部分结果如下，其中Address元素由Address，City及PostalCode构成。 Vins et alcools Chevalier Paul Henriot 59 rue de lAbbaye Reims 51100 Toms Spezialit?ten Karin Josephs Luisenstr. 48 Mnster 44087 代码：SELECT Order.OrderID Order/OrderID, OrderDate Order/OrderDate, RequiredDateOrder/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 OrderINNER JOIN Customers CustomerON Order.CustomerID=Customer.CustomerIDORDER BY Order.OrderIDFOR XML PATH()6、选做：使用FOR XML PATH模式，检索10248号订单的相关信息，结果如下： Vins et alcools Chevalier Paul Henriot 59 rue de lAbbaye Reims 51100 Queso Cabrales 21.0000 12 0.0000000e+000 Vins et alcools Chevalier Paul Henriot 59 rue de lAbbaye Reims 51100 Singaporean Hokkien Fried Mee 14.0000 10 0.0000000e+000 Vins et alcools Chevalier Paul Henriot 59 rue de lAbbaye Reims 51100 Mozzarella di Giovanni 34.8000 5 0.0000000e+000 代码：SELECT Order.OrderID Order/OrderID, OrderDate Order/OrderDate, RequiredDateOrder/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/ProductProductD, ProductName Order/ITemList/Product/ProductName, Quantity Order/ItemList/Product/Quantity, Discount Ordep/ItemList/Product/Discount FROM Orders Order,Customers Customer,Order Details,ProductsWHERE Order.CustomerID=Customer.CustomerID AND Order Details.OrderID=Order.OrderID AND Order Details.ProductID=Products.ProductIDAnd Order.OrderID=10248FOR XML PATH()7、使用FOR XML PATH模式，检索每个订单、产品及其客户的相关信息，输出前两条记录，结果如下。 Vins et alcools Chevalier Paul Henriot 59 rue de lAbbaye Reims 51100 Toms Spezialit?ten Karin Josephs Luisenstr. 48 Mnster 44087 代码：SELECT TOP 2 Orders.OrderID Order/OrderID, OrderDate Order/OrderDate, RequiredDateOrder/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 CustomerWHERE Orders.CustomerID=Customer.CustomerID ORDER BY Orders.OrderIDFOR 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!QuantityFROM Orders,Customers,Order Details,Products WHERE Orders.OrderID=Order Details.OrderID AND Order Details.ProductID=Products.ProductID AND Orders.CustomerID=Customers.CustomerIDUNION ALLSELECT 2 AS TAG, 1 AS PARENT, Orders.OrderID, Customers.CustomerID, OrderDate, Products.ProductID, Order Details.UnitPrice, QuantityFROM Orders,Customers,Order Details,Products WHERE Orders.Or