SQL Server与XML 数据

  Server中的FOR XML语句是扩展SELECT的语法，它主要返回XML代替了行和列,可配置用于返回属性，元素和架构其常用RAW 、AUTO、PATH模式查询.

USE [Northwind]
GO
CREATE PROCEDURE [dbo].[usp_GetProductsXmlAuto]
AS
--demo1
SELECT [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
  FROM [Products] 
  FOR XML AUTO,ROOT('Products')
--demo2 
--SELECT [ProductID]
--    ,[ProductName]
--    ,[SupplierID]
--    ,[CategoryID]
--    ,[QuantityPerUnit]
--    ,[UnitPrice]
--    ,[UnitsInStock]
--    ,[UnitsOnOrder]
--    ,[ReorderLevel]
--    ,[Discontinued]
--FROM [Products] 
--FOR XML RAW('Product'),ROOT('Products')
--demo3 
--SELECT     Products.ProductName,
--		   Products.ProductID, 
--		   Products.SupplierID, 
--		   OrderDetails.UnitPrice, 
--		   OrderDetails.Quantity, 
--           OrderDetails.Discount
--FROM       OrderDetails INNER JOIN
--           Products ON OrderDetails.ProductID = Products.ProductID
-- ORDER BY Products.ProductID
--  FOR XML AUTO,ROOT('Orders')

USE [Northwind]
GO
CREATE PROCEDURE [dbo].[usp_GetProductsXmlXPath]
AS
--demo1
--SELECT [ProductID]
--      ,[ProductName]
--      ,[SupplierID]
--      ,[CategoryID]
--      ,[QuantityPerUnit]
--      ,[UnitPrice]
--      ,[UnitsInStock]
--      ,[UnitsOnOrder]
--      ,[ReorderLevel]
--      ,[Discontinued]
--  FROM [Products] 
--  FOR XML Path,ROOT('Products')
 --demo2
  SELECT [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      --,[UnitsInStock]
      --,[UnitsOnOrder]
      --,[ReorderLevel]
      --,[Discontinued]
  FROM [Products] 
  FOR XML Path('Category'),ROOT('Products')