xmlandxquery.sql

< SQL Server2005程序设计>

/*
Programming SQL Server 2005 (MS Press)
By: Andrew Brust and Stephen Forte 
Chapter 4
XQuery, XML, and the XML DataType 
Samples By: Stephen Forte ([e-mail])
*/

--Working with the XML Data Type as a Variable

USE AdventureWorks

DECLARE @xmlData AS XML --new datatype
Set @xmlData='
<Customers>
  <CustomerID>CORZN</CustomerID> 
  <CompanyName>Corzen, Inc</CompanyName> 
  <ContactName>Stephen Forte</ContactName> 
  <ContactTitle>Sales Representative</ContactTitle> 
  <Address>5-9 Union Square West</Address> 
  <City>New York</City> 
  <PostalCode>10028</PostalCode> 
  <Country>USA</Country> 
  <Phone>030-0074321</Phone> 
  <Fax>030-0076545</Fax> 
  </Customers>
'
Select @xmlData

--Create an XML column in a table
USE AdventureWorks
GO
--create the table with the XML Datatype
CREATE TABLE OrdersXML 
	(OrderDocID INT PRIMARY KEY, 
	xOrders XML NOT NULL)

--Insert Static XML via a variable
USE AdventureWorks
GO
--Insert Static XML via a variable
DECLARE @xmlData AS XML
SET @xmlData = '
<Orders>
	<Order>
	<OrderID>5</OrderID>
	<CustomerID>65</CustomerID>
	<OrderAmount>25</OrderAmount>
	</Order>
</Orders>'
--insert into the table
INSERT INTO OrdersXML (OrderDocID, xOrders) Values (1, @xmlData)

--using defaults
USE AdventureWorks
GO
CREATE TABLE OrdersXML 
	(OrderDocID INT PRIMARY KEY, 
	xOrders XML NOT NULL DEFAULT '<Orders/>'

--The following insert works because it relies on the default:
INSERT INTO OrdersXML (OrderDocID, xOrders) VALUES (2, DEFAULT)

--Constraints and Defaults
USE AdventureWorks
GO
DROP TABLE OrdersXML
--create the table with the XML Datatype
--using a default value
--also has the check constraints for <Order>
CREATE TABLE OrdersXML 
	(OrderDocID INT PRIMARY KEY, 
	xOrders XML NOT NULL Default '<Orders/>'
 CONSTRAINT xml_orderconstraint
  CHECK(xOrders.exist('/Orders')=1))

--Schema
USE AdventureWorks
GO
--new syntax for adding a schema 
--schema can disallow fragments if you say so
CREATE XML SCHEMA COLLECTION dbo.customer_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:tns="http://corzen.com/customer"
            targetNamespace="http://corzen.com/customer" >
   <xsd:simpleType name="customerAge" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="65" />
       <xsd:minExclusive value="18" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="custage" type="tns:customerAge" />
</xsd:schema>'
GO

--Create a table with the schema attached
USE AdventureWorks
GO
--create table with xml column 
--and use schema as a rule for 
--a particular column
CREATE TABLE xml_schematest (
   Customer_ID int primary key, 
   CustomerAge_XML XML(customer_xsd) --XML Schema Name
) 
GO
--Test the Schema
USE AdventureWorks
GO
-- works
INSERT INTO xml_schematest VALUES(1, 
 '<p:custage xmlns:p="http://corzen.com/customer">55</p:custage>')
GO

-- fails, age > 65
INSERT INTO xml_schematest VALUES(3, 
 '<p:custage xmlns:p="http://corzen.com/customer">105</p:custage>')
GO

--XML index Examples
USE AdventureWorks--but can be any DB

--Drop the table since we used
--it before
Drop Table OrdersXML

--Create the table with a PK
--and an XML column
CREATE TABLE OrdersXML 
	(OrderDocID INT PRIMARY KEY, 
	xOrders XML NOT NULL)

--Insert Some data into our new
--table here
INSERT INTO OrdersXML 
Values (1, '
<Orders>
	<Order>
	<OrderID>5</OrderID>
	<CustomerName>Stephen Forte</CustomerName>
	<OrderAmount>25</OrderAmount>
	</Order>
</Orders>')

INSERT INTO OrdersXML 
Values (2, '
<Orders>
	<Order>
	<OrderID>7</OrderID>
	<CustomerName>Andrew Brust</CustomerName>
	<OrderAmount>45</OrderAmount>
	</Order>
</Orders>')

INSERT INTO OrdersXML 
Values (3, '
<Orders>
	<Order>
	<OrderID>2</OrderID>
	<CustomerName>Bill Zack</CustomerName>
	<OrderAmount>65</OrderAmount>
	</Order>
</Orders>')

CREATE Primary XML INDEX idx_1 
	ON OrdersXML (xOrders)

-- display the columns in the node table
SELECT col.column_id, col.object_id, 
	col.name, col.system_type_id
FROM sys.columns col
	inner JOIN sys.indexes idx 
	ON idx.object_id = col.object_id
WHERE idx.name = 'idx_1'
 AND idx.type = 1
ORDER BY column_id

--structural (Path)
CREATE XML INDEX idx_a ON OrdersXML (xOrders)
USING XML INDEX idx_1 FOR PATH 
--value
CREATE XML INDEX idx_b ON OrdersXML (xOrders)
USING XML INDEX idx_1 FOR VALUE
--property
CREATE XML INDEX idx_c ON OrdersXML (xOrders)
USING XML INDEX idx_1 FOR PROPERTY 

--For XML Examples
--For XML RAW
USE AdventureWorks
GO

SELECT TOP 10 -- limits the result rows for demo purposes
	Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.OrderDate
FROM Sales.Customer Customer
INNER JOIN Sales.SalesOrderHeader OrderHeader 
	ON OrderHeader.CustomerID = Customer.CustomerID
ORDER BY Customer.CustomerID
FOR XML RAW 

--For XML Auto
USE AdventureWorks
GO

SELECT TOP 10 -- limits the result rows for demo purposes
	Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.OrderDate
FROM Sales.Customer Customer
INNER JOIN Sales.SalesOrderHeader OrderHeader
	ON OrderHeader.CustomerID = Customer.CustomerID
ORDER BY Customer.CustomerID
FOR XML AUTO

--FOR XML EXPLICIT
USE AdventureWorks
GO

SELECT TOP 2 -- limits the result rows for demo purposes
	1 AS Tag,
	NULL AS Parent,
	CustomerID AS [Customer!1!CustomerID], 
	NULL AS [SalesOrder!2!SalesOrderID], 
	NULL AS [SalesOrder!2!OrderDate]
FROM Sales.Customer AS Customer

UNION ALL

SELECT TOP 10 -- limits the result rows for demo purposes
	2,
	1,
	Customer.CustomerID, 
	OrderHeader.SalesOrderID, 
	OrderHeader.OrderDate
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader AS OrderHeader
	ON OrderHeader.CustomerID = Customer.CustomerID

ORDER BY [Customer!1!CustomerID], [SalesOrder!2!SalesOrderID]
FOR XML EXPLICIT

--Element
USE AdventureWorks
GO
--XML EXPLICIT 
SELECT TOP 2 -- limits the result rows for demo purposes
	1 AS Tag,
	NULL AS Parent,
	CustomerID AS [Customer!1!CustomerID], 
	NULL AS [SalesOrder!2!SalesOrderID], 
	NULL AS [SalesOrder!2!OrderDate!ELEMENT] --Render as an element
FROM Sales.Customer AS Customer

UNION ALL

SELECT TOP 10 -- limits the result rows for demo purposes
	2,
	1,
	Customer.CustomerID, 
	OrderHeader.SalesOrderID, 
	OrderHeader.OrderDate
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader AS OrderHeader
	ON OrderHeader.CustomerID = Customer.CustomerID

ORDER BY [Customer!1!CustomerID], [SalesOrder!2!SalesOrderID]
FOR XML EXPLICIT

--Explicit 
USE AdventureWorks
GO
--XML EXPLICIT 
SELECT --TOP 2 -- limits the result rows for demo purposes
	1 AS Tag,
	NULL AS Parent,
	CustomerID AS [Customer!1!CustomerID], 
	NULL AS [SalesOrder!2!SalesOrderID], 
	NULL AS [SalesOrder!2!TotalDue],
	NULL AS [SalesOrder!2!OrderDate!ELEMENT],
	NULL AS [SalesOrder!2!ShipDate!ELEMENT],
	NULL AS [SalesDetail!3!ProductID],
	NULL AS [SalesDetail!3!OrderQty],
	NULL AS [SalesDetail!3!LineTotal]
FROM Sales.Customer AS Customer
WHERE Customer.CustomerID IN (1, 2)

UNION ALL

SELECT 
	2,
	1,
	Customer.CustomerID, 
	OrderHeader.SalesOrderID, 
	OrderHeader.TotalDue,
	OrderHeader.OrderDate,
	OrderHeader.ShipDate,
	NULL,
	NULL,
	NULL
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader AS OrderHeader
	ON OrderHeader.CustomerID = Customer.CustomerID
WHERE Customer.CustomerID IN (1, 2)

UNION ALL

SELECT 
	3,
	2,
	Customer.CustomerID, 
	OrderHeader.SalesOrderID, 
	OrderHeader.TotalDue,
	OrderHeader.OrderDate,
	OrderHeader.ShipDate,
	OrderDetail.ProductID,
 	OrderDetail.OrderQty,
	OrderDetail.LineTotal
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader AS OrderHeader
	ON OrderHeader.CustomerID = Customer.CustomerID
INNER JOIN Sales.SalesOrderDetail AS OrderDetail
	ON OrderDetail.SalesOrderID = OrderHeader.SalesOrderID
WHERE Customer.CustomerID IN (1, 2)

ORDER BY [Customer!1!CustomerID], [SalesOrder!2!SalesOrderID]
FOR XML EXPLICIT

--FOR XML TYPE
DECLARE @xmlData AS XML

SET @xmlData = 
(Select Customer.CustomerID, OrderDetail.SalesOrderID,
	OrderDetail.OrderDate
From Sales.Customer Customer
	inner join Sales.SalesOrderHeader OrderDetail 
	on OrderDetail.customerid=Customer.customerid
WHERE Customer.CustomerID<3
ORDER BY Customer.CustomerID
For XML AUTO, TYPE)--Casts to XML type
SELECT @xmlData

--outer with TYPE
USE AdventureWorks
GO

SELECT 
	CustomerID,
	(SELECT SalesOrderID, 
			TotalDue, 
			OrderDate, 
			ShipDate
	 FROM Sales.SalesOrderHeader AS OrderHeader
	 WHERE CustomerID = Customer.CustomerID 
	 FOR XML AUTO, TYPE) AS OrderHeaders
FROM Sales.Customer AS Customer
WHERE CustomerID IN (1, 2)

--PATH
USE AdventureWorks
GO
--XML FOR PATH
SELECT TOP 2 --limits result rows for demo purposes
ContactID AS [@Contact_ID],
FirstName AS [ContactName/First], 
LastName AS [ContactName/Last],
Phone AS [ContactPhone/Phone1]
FROM Person.Contact FOR XML PATH

--For Path
USE AdventureWorks
GO

SELECT 
	CustomerID AS [@CustomerID],
	(SELECT SalesOrderID AS [@SalesOrderID], 
			TotalDue AS [@TotalDue], 
			OrderDate, 
			ShipDate,
			(SELECT ProductID AS [@ProductID], 
					OrderQty AS [@OrderQty], 
					LineTotal AS [@LineTotal] 
			 FROM Sales.SalesOrderDetail
			 WHERE SalesOrderID = OrderHeader.SalesOrderID
			 FOR XML PATH('OrderDetail'), TYPE) 
	 FROM Sales.SalesOrderHeader AS OrderHeader
	 WHERE CustomerID = Customer.CustomerID 
	 FOR XML PATH('OrderHeader'), TYPE)
FROM Sales.Customer AS Customer
WHERE CustomerID IN (1, 2)
FOR XML PATH ('Customer')

--XML PATH comment and data
SELECT 
	Customer.CustomerID AS [@CustomerID],
	Contact.FirstName + ' ' + Contact.LastName AS [comment()],
	(SELECT SalesOrderID AS [@SalesOrderID], 
			TotalDue AS [@TotalDue], 
			OrderDate, 
			ShipDate,
			(SELECT ProductID AS [data()]
			 FROM Sales.SalesOrderDetail
			 WHERE SalesOrderID = OrderHeader.SalesOrderID