實(shí)驗(yàn)六管理XML數(shù)據(jù)

1、實(shí)驗(yàn)六管理XML數(shù)據(jù)、目標(biāo)完成這個(gè)實(shí)驗(yàn)后，你將能夠:1使用XML結(jié)構(gòu)2、檢索XML數(shù)據(jù)二、實(shí)驗(yàn)內(nèi)容實(shí)驗(yàn)開始前，請(qǐng)執(zhí)行腳本instnwnd.sql，安裝 Northwind數(shù)據(jù)庫。本實(shí)驗(yàn)均在Northwind數(shù)據(jù)庫中完成。本實(shí)驗(yàn)為 6學(xué)時(shí)。練習(xí)1使用XML結(jié)構(gòu)1 創(chuàng)建一個(gè)含有XML類型字段的數(shù)據(jù)表。以示例數(shù)據(jù)庫Northwind為例，在該數(shù)據(jù)庫中創(chuàng)建一個(gè)名為xml_Categories的數(shù)據(jù)表,該數(shù)據(jù)表中包含兩個(gè)字段，如下表所示。字段名類 型CategoryIDInt，標(biāo)識(shí)，且為主鍵Category InfoXMLCREATE TABLE xml_Categories (CategorylD I

2、NT IDENTITY PRIMARY KEY,CategoryI nfo XML);2、 向該數(shù)據(jù)表輸入一條記錄，CategoryInfo字段值如下：Beverages Soft drin ks, coffees, teas, beers, and alesv/Descripti onINSERT INTO xml_Categories ( CategoryInfo )VALUES ( vCategoryInfoBeveragesSoftdrin ks,coffees,teas,beers,andalesv/Descripti on v/Category lnfo);3、查詢表中信息SELE

3、CT * FROM xml_Categories結(jié)果蛋消息Category DCategorplnfo1Bev&raa8s7CateaciuNa. Beverages Soft dr in ks,coffees, teas, beers, and ales 4、創(chuàng)建XML數(shù)據(jù)類型變量，定義 XML變量Categorylnfo，將其值設(shè)置為vCategory lnfoCon dime ntsSweet and savory sauces, relishes, spreads, and seasoningsDECLARE CategoryI nfo XMLSET CategoryI nfo =Co

4、n dime ntsSweet and savory sauces, relishes, spreads, and seas oningsSELECT CategoryI nfoCon dime ntsSweet and savory sauces, relishes, spreads, and seas onings5、將Categorylnfo 值插入 xml_Categories 表中。DECLARE CategoryI nfo XMLSET CategoryI nfo =Con dime ntsSweet and savory sauces, relishes, spreads, an

5、d seas oningsINSERT xml_Categories ( CategoryInfo)VALUES ( CategoryInfo)SELECT * FROM xml_Categories盲錯(cuò)果s消息Category IDCategoMnfo1 H CleaorvNB ever aae$/Cat eaoi uN 白2 2Condiments /.33口 口呵 CateaoryNCondiments/.-.44Cate 口 o 沖匚日畑忖弓 meCondiments CREATE XML SCHEMA COLLECTION testXMLSchemaASvschema xml ns

6、=/2001/XMLSchemaI2、 創(chuàng)建一個(gè)名為xsd_Categories的數(shù)據(jù)表，包含一個(gè)Categories字段，為XML類型，并將 架構(gòu)集合testXMLSchema與其關(guān)聯(lián)。CREATE TABLE xsd_Categories (Categories XML( testXMLSchema )3、向數(shù)據(jù)表xsc_Categories中插入一條記錄，值為1ca ndy chocolate,pa no chav/Category lnfoINSERT xsd_CategoriesVALUES ( vCategorylnfo1 ca ndy vDescr

7、ipti on chocolate,pa no cha )Select * from xsd_Categories13結(jié)果鳥消息1Categories1 1 匚 Meqmy “.4、將上面的Transact SQL腳本稍作改動(dòng)，將1更改為abcd結(jié)果如何？為什么？| 消息|甫息字3級(jí)緲”応?duì)顟B(tài)丄，第1行XHL 驗(yàn)證：簡單類型值 1 abed1 無敕 q 位買：/*: Category Into 1 /*: Categaryldd I原因是testXMLSchema中定義的CategoryId的類型是integer練習(xí)3:檢索XML數(shù)據(jù)1、使用FOR XML RAW 模式，從Products表中

8、輸出前5條記錄，結(jié)果如下。SELECT TOP 5 ProductID , ProductName , SupplierID , CategoryIDfrom ProductsORDER BY ProductIDFOR XML RAW口結(jié)果魚消息XMLF52E2B61J 9A1 11 d!創(chuàng) UU0805F49916B1 I網(wǎng) PeductlD=T ProductNaniTCIW Sum加g =2、使用FOR XML RAW 模式，從Products表中輸出前5條記錄，結(jié)果如下vProduct 1 Chai 12 1 2Chang 12 1 3Aniseed Syrup12 24Chef An

9、tons Cajun Seas oning/ ProductName225Chef Antons Gumbo Mix22SELECTProductNameCategory . CategorylD , CategoryName , Description, ProductIDFROM Categories CategoryINNER JOIN Products ProductON Category . CategoryID =Product . CategoryIDWHERECategory . CategoryID IN( 1, 2)ORDER BY Category . CategoryI

10、DFOR XML AUTO結(jié)果消息XML_F52E2BE1 -10A1 11dVB1 D500S05F49916Bt忙a怕Ca佢口mID=1 。怕口。麗師討昭恥曲3、使用 FOR XML AUTO 模式，從 Categories 和 Products 表中輸出 CategoryID 為 1 或2的記錄，包括每種類別的編號(hào)、名稱、描述信息以及該種類別的產(chǎn)品信息，結(jié)果如下。/CategorySELECTProductID=65 ProductName=LouisianaFiery Hot Pepper SauceI!I!,ProductID,ProductName,CategoryName , D

11、escriptionCategory . CategorylDFROM Categories CategoryINNER JOIN Products ProductON Category . CategoryID =Product . CategoryIDWHERECategory . CategorylD IN( 1, 2)ORDER BY Category . CategorylDFOR XML AUTO方站果消息-XML.FKtiBbiiaAi -ildiidooeosFgiGB1 Cateaory CateaarvlD =1 CateaarvName-Beveraae.:4、使用 FO

12、R XML AUTO 模式，從 Categories 和 Products 表中輸出 CategoryID 為 1 或2的記錄，包括每種類別的編號(hào)、名稱、描述信息以及該種類別的產(chǎn)品信息，結(jié)果如下。1 BeveragesSoftdri nks.coffees,teas, beers, and ales 1 Chai 2Chang ProductID 24 / ProductIDGuaran a FantsticaProductID 34 / ProductID Sasquatch AleProductID 35 / ProductID Steeleye StoutProductID 38 / P

13、roductIDC?te de BlayeProductID 39 / ProductIDProductName Chartreuse verte43 Ipoh Coffee / ProductName67 Laughing Lumberjack Lager70 Outback Lager75 Rh?nbr?u Klosterbier 76 Lakkalik?ri2 Condiments Sweet and savory sauces, relishes, spreads, andseasonings 3/ ProductID Aniseed Syrup4Chef Antons Cajun S

14、easoning/ ProductName5Chef Antons Gumbo Mix 6 Grandmas Boysenberry Spread/ ProductName8 Northwoods Cranberry SauceProductID 15/ ProductIDGenen ShouyuProductID 44/ ProductIDGula MalaccaProductID 61/ ProductIDSirop d e rableProductID 63/ ProductIDVegie-spreadProductID 65Louisiana Fiery Hot Pepper Sauc

15、eProductID 66Louisiana Hot Spiced OkraProduct ProductID 77 Original Frankfurter grProduct u ne So?eSELECTCategory . CategoryID , CategoryName , Description , ProductID , ProductName FROM Categories CategoryINNER JOIN Products ProductON Category . CategoryID =Product . CategoryIDWHERECategory . Categ

16、oryID IN( 1, 2)ORDER BY Category . CategoryIDFOR XML AUTO, ELEMENTS5、使用 FOR XML PATH 模式，檢索每個(gè)訂單及其客戶的相關(guān)信息，部分結(jié)果如下，其中Address 元素由 Address ， City 及 PostalCode 構(gòu)成。Vins et alcools ChevalierPaul Henriot 59 rue de lAbbaye Reims 51100Toms Spezialit?tenKarin JosephsLuisenstr. 48 Mu nster 44087SELECT OrderOrderD

17、ate. OrderID Order/OrderID,Order/OrderDate,RequiredDateCustomerCompanyName ContactNameAddressOrder/Order,. CustomerIDOrder/Customer/CustomerIDOrder/Customer/CompanyName,Order/Customer/ContactName,Order/Customer/Address/data()City Order/Customer/Address/data()PostalCodeOrder/Customer/Address/data()FR

18、OM Orders OrderINNER JOIN Customers CustomerON Order. CustomerID = Customer . CustomerIDORDER BY Order . OrderIDFOR XML PATH( )6、選做：使用 FOR XML PATH 模式，檢索 10248 號(hào)訂單的相關(guān)信息，結(jié)果如下：Vins et alcools ChevalierPaul Henriot59 rue de lAbbaye Reims 51100Queso Cabrales/ ProductName21.0000120.0000000e+000Vins et al

19、cools ChevalierPaul Henriot59 rue de lAbbaye Reims 51100 Singaporean Hokkien Fried Mee/ ProductName 14.0000 100.0000000e+000 Vins et alcools ChevalierPaul HenriotAddress 59 rue de lAbbaye Reims 51100 Mozzarella di Giovanni 34.8000 5Discount0.0000000e+000SELECT Order OrderDate RequiredDate. OrderID O

20、rder/OrderIDOrder/OrderDateOrder/Order ,Customer . CustomerID Order/Customer/CustomerIDCompanyNameContactNameAddressOrder/Customer/CompanyName Order/Customer/ContactName Order/Customer/Address/data()City Order/Customer/Address/data() , PostalCode Order/Customer/Address/data() ,Order Details . Produc

21、tID Order/ItemList/Product/ProductID ProductNameOrder/ItemList/Product/ProductNameQuantityDiscountFROM Orders OrderOrder/ItemList/Product/QuantityOrdep/ItemList/Product/Discount, Customers Customer , Order Details, ProductsWHEREOrder . CustomerID = Customer . CustomerID AND OrderDetails . OrderID =O

22、rder . OrderID AND OrderDetails . ProductID = Products . ProductIDAnd Order . OrderID =10248FOR XML PATH( )7、使用 FOR XML PATH 模式，檢索每個(gè)訂單、 產(chǎn)品及其客戶的相關(guān)信息， 輸出前兩條記錄， 結(jié)果如下。Vins et alcools ChevalierPaul HenriotAddress 59 rue de lAbbaye Reims 51100Toms Spezialit?ten Karin JosephsAddress Luisenstr. 48 Mu nster

23、44087SELECT TOP 2 Orders . OrderID Order/OrderIDOrderDateOrder/OrderDate ,RequiredDateOrder/RequiredDate ,Customer. CustomerID Order/Customer/CustomerIDCompanyNameOrder/Customer/CompanyName ,ContactNameOrder/Customer/ContactName ,AddressOrder/Customer/Address/data() ,City Order/Customer/Address/data

24、()PostalCode (Order/Customer/Address/data() ,SELECT Products . ProductIDProductName ,Products. UnitPrice ,Quantity FROMProducts , Order Details LWHEREProducts . ProductID =L. ProductID AND L. OrderID = Orders . OrderIDFOR XML RAW( Product ), TYPE) Order/ProductsFROM Orders , Customers CustomerWHEREO

25、rders . CustomerID = Customer . CustomerIDORDER BY Orders. OrderIDFOR XML PATH( ), ROOT( Orders )= 14.0000 Quantity= 12 /= 9.8000 Quantity= 10 /= 34.8000 Quantity= 5 /= TOMSP= 18.6000 Quantity= 9 /= 42.4000 Quantity= 40 /= VINET OrderDate =1996-07-04T00:00:00ProductProductID= 11UnitPriceProductProductID= 42UnitPriceProductProductID= 72UnitPrice Order OrderID = 10248 CustomerID8、使用 FOR XML EXPLICIT 模式，檢索每個(gè)訂單的相關(guān)信息，部分結(jié)果如下。 ProductProductID= 14UnitPriceProductProductID= 51UnitPriceSELECT DISTINCT 1 AS TAG , NULL AS PARENT ,Orders . OrderI