ch20.sql

SQLServer2005基础教程,清华大学出版社

DROP TABLE ProductInfo
USE ETravelCom
CREATE TABLE ProductInfo(
ProductID INT PRIMARY KEY,
ProductDesc XML)

 
SELECT * FROM ProductInfo

USE ETravelCom 
INSERT INTO ProductInfo 
VALUES(1, 
'<ProductDesc> 
	<Product_ID>EOS400D</Product_ID>
	<Product_Name>佳能数码照相机</Product_Name>
	<Product_Overview>1000万有效像素，大型CMOS感应器。</Product_Overview>
</ProductDesc>') 

DELETE FROM ProductInfo

USE ETravelCom
INSERT INTO ProductInfo
VALUES(2,
'<ProductDesc>
	<Product_ID>Inspiron640m</Product_ID>
	<Product_Name>戴尔笔记本电脑</Product_Name>
	<Product_Overview>英特尔迅弛双核移动技术，80GBSATA硬盘。
</ProductDesc>')




USE ETravelCom
INSERT INTO ProductInfo
VALUES(3,
'<ProductDesc>
	<Product_ID>IPOD123</Product_ID>
	<Product_Name>苹果mp3</Product_Name>
</ProductDesc>')


USE ETravelCom
CREATE XML SCHEMA COLLECTION ProductDescXMLSchema
AS
N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
           elementFormDefault="qualified" 
           targetNamespace="http://ProductDescSchema">
	<xs:element name="ProductDesc">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="Product_ID" type="xs:string" />
				<xs:element name="Product_Name" type="xs:string" />
				<xs:element name="Product_Overview" type="xs:string" />
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>'



USE ETravelCom
CREATE TABLE ProductInfo(
ProductID INT PRIMARY KEY,
ProductDesc XML(ProductDescXMLSchema))



USE ETravelCom 
INSERT INTO ProductInfo 
VALUES(2, 
'<ProductDesc xmlns="http://ProductDescSchema"> 
	<Product_ID>Inspiron640m</Product_ID>
	<Product_Name>戴尔笔记本电脑</Product_Name>
</ProductDesc>') 









USE ETravelCom 
INSERT INTO ProductInfo 
VALUES(1, 
'<ProductDesc xmlns="http://ProductDescSchema"> 
	<Product_ID>EOS400D</Product_ID>
	<Product_Name>佳能数码照相机</Product_Name>
	<Product_Overview>1000万有效像素，大型CMOS感应器。</Product_Overview>
</ProductDesc>') 











SELECT *
FROM sys.xml_schema_collections

USE ETravelCom
SELECT XML_SCHEMA_NAMESPACE(N'dbo', N'ProductDescXMLSchema')



USE ETravelCOm
DECLARE @Product XML
SET @Product = '<Product>
					<Product_ID>EOS400D</Product_ID>
					<Product_Name>佳能数码照相机</Product_Name>
				</Product>'
SELECT @Product.query('/Product')

USE ETravelCOm
DECLARE @Product XML
SET @Product = '<Product>
					<Product_ID>EOS400D</Product_ID>
					<Product_Name>佳能数码照相机</Product_Name>
				</Product>'
SELECT @Product.query('/Product/Product_Name')


USE ETravelCom
SELECT ProductDesc.query('declare namespace tns="http://ProductDescSchema";
                          /tns:ProductDesc/..') As '产品信息'
FROM ProductInfo



USE ETravelCom
SELECT *
FROM ProductInfo
WHERE ProductDesc.exist('declare namespace tns="http://ProductDescSchema";
                         /tns:ProductDesc/tns:Product_ID="EOS400D"')=1


USE ETravelCom
UPDATE ProductInfo
SET ProductDesc.modify('declare namespace tns="http://ProductDescSchema";
                        replace value of (/tns:ProductDesc/tns:Product_Name)[1]
                        with "Canon Digital Camera"')
WHERE ProductID = 1






USE AdventureWorks
SELECT title, FirstName, LastName 
FROM Person.Contact FOR XML RAW  



USE AdventureWorks
SELECT title, FirstName, LastName 
FROM Person.Contact FOR XML AUTO



SELECT title, FirstName, LastName
FROM Person.Contact FOR XML AUTO 


SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO


SELECT title, FirstName, LastName 
FROM Person.Contact FOR XML EXPLICIT  






SELECT 1        	as Tag,
        NULL 		as Parent,
        EmployeeID 	as [Employee!1!EmpID],
        NULL      	as [Name!2!FName],
        NULL      	as [Name!2!LName]
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
UNION ALL
SELECT 2    		as Tag,
        1 			as Parent,
        EmployeeID,
        FirstName, 
        LastName 
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT





USE ETravelCom
SELECT ProductID, ProductDesc
FROM ProductInfo
WHERE ProductID = 1
FOR XML AUTO, TYPE


SELECT title "Employee/Title",
        FirstName "Employee/First_Name",
        LastName "Employee/Last_Name"
FROM Person.Contact FOR XML PATH


SELECT (SELECT title, FirstName, LastName
        FROM Person.Contact
        FOR XML RAW, TYPE, ROOT('root')).query('/root[1]/row[1]')


USE ETravelCom
SELECT ProductDesc
FROM ProductInfo
WHERE ProductID = 1
FOR XML AUTO, XMLSCHEMA



USE ETravelCom
-- 创建一个主XML索引
CREATE PRIMARY XML INDEX PXML_ProductInfo
ON ProductInfo(ProductDesc)
GO
-- 创建一个PATH类型辅助索引
CREATE XML INDEX IXML_PATH_ProductInfo
ON ProductInfo(ProductDesc)
USING XML INDEX PXML_ProductInfo FOR PATH
GO






USE ETravelCom
DECLARE @hproduct INT
DECLARE @ProductDesc VARCHAR(1000)
SET @ProductDesc = '<ProductDesc>
						<Product_ID>EOS400D</Product_ID>
						<Product_Name>Canon Digital Camera</Product_Name>
					</ProductDesc>'
EXEC sp_xml_preparedocument @hproduct OUTPUT, @ProductDesc
SELECT * FROM OPENXML(@hproduct, '/ProductDesc', 10)
WITH (Product_ID NVARCHAR(20),
      Product_Name NVARCHAR(50))
EXEC sp_xml_removedocument @hproduct