管理XML数据(3)

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、编写脚本，插入节点，结果如下