xmlandxquery.sql

< SQL Server2005程序设计>

			 FOR XML PATH('')) AS [ProductIDs] 
	 FROM Sales.SalesOrderHeader AS OrderHeader
	 WHERE CustomerID = Customer.CustomerID 
	 FOR XML PATH('OrderHeader'), TYPE)
FROM Sales.Customer AS Customer
INNER JOIN Sales.Individual AS Individual 
	ON Customer.CustomerID = Individual.CustomerID
INNER JOIN Person.Contact AS Contact 
	ON Contact.ContactID = Individual.ContactID
WHERE Customer.CustomerID IN (11000, 11001)
FOR XML PATH ('Customer')

--Root
USE AdventureWorks
GO
SELECT Customer.CustomerID, 
	OrderDetail.SalesOrderID, OrderDetail.OrderDate
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader OrderDetail 
		ON OrderDetail.customerid=Customer.customerid
WHERE Customer.CustomerID<20
ORDER BY Customer.CustomerID
FOR XML AUTO, ROOT ('Orders')

--Schema
USE AdventureWorks
GO
SELECT Customer.CustomerID, 
	OrderDetail.SalesOrderID, OrderDetail.OrderDate
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader AS OrderDetail 
		ON OrderDetail.CustomerID = Customer.CustomerID
WHERE Customer.CustomerID < 20
ORDER BY Customer.CustomerID
FOR XML AUTO, XMLSCHEMA

--Elements
Select Customer.CustomerID, OrderDetail.SalesOrderID,OrderDetail.OrderDate
From Sales.Customer Customer
inner join Sales.SalesOrderHeader OrderDetail 
		on OrderDetail.customerid=Customer.customerid
where Customer.CustomerID=1
Order by Customer.CustomerID
For XML AUTO, ELEMENTS

--OpenXML

USE AdventureWorks

DECLARE @int int
DECLARE @xmlORDER varchar(1000)
SET @xmlORDER ='
<ROOT>
<Customer CustomerID="BRU" ContactName="Andrew Brust">
   <Order CustomerID="BRU" EmployeeID="5" OrderDate="2005-11-04">
      <OrderDetail OrderID="10248" ProductID="16" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="32" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="ZAC" ContactName="Bill Zack">
   <Order CustomerID="ZAc" EmployeeID="3" OrderDate="2005-11-16">
      <OrderDetail OrderID="10283" ProductID="99" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML doc
EXEC sp_xml_preparedocument @int OUTPUT, @xmlORDER
-- OPENXML rowset provider.
SELECT    *
FROM OPENXML (@int, '/ROOT/Customer',1)
WITH (CustomerID  varchar(10),ContactName varchar(20))

--OpenRowSet (bulk provider)
/*
Using XML Bulk Load
*/
Use AdventureWorks
--create a table with an xml column
create table tblxmlcustomers 
(customer_id int primary key identity, 
	customer_xml xml not null)

--this file will load 1 record in (SINGLE_CLOB)
--for more records use a format file
insert into tblxmlcustomers
Select * from OPENROWSET
(Bulk 'C:\customer_01.xml',
	SINGLE_CLOB) as xmldata

--take a look at the data in the table
select * from tblxmlcustomers

--XQuery
--simple XQuery Expression
USE AdventureWorks
Go
DECLARE @XML xml
Set @XML='<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Bill Zack</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Developing ADO .NET</title>
    <author>Andrew Brust</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Windows Cluster Server</title> 
    <author>Stephen Forte</author> 
    <price>59.99</price> 
  </book>
</catalog>
'
Select @XML.query('for $b in /catalog/book
	where $b/@category="ITPro"
      order by $b/author[1] descending 
   return ($b)')

USE AdventureWorks
Go

--Schema for our table
CREATE xml schema collection dbo.classes_xsd
As
 '<?xml version="1.0" encoding="UTF-8" ?> 
  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="class">
  <xs:complexType>
  <xs:attribute name="name" type="xs:string" use="required" /> 
  </xs:complexType>
  </xs:element>
  <xs:element name="classes">
  <xs:complexType>
  <xs:sequence>
  <xs:element ref="class" maxOccurs="unbounded" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:schema>'

--table for our examples, uses the XSD schema
Create Table tblSpeakers
(
Speaker_ID Integer Primary Key Identity,
Speaker_NM nVarChar(50),
Speaker_Country nVarChar(25),
c XML (classes_xsd) Not Null)

--XML Index: Primary
CREATE Primary XML INDEX idx_1 
	ON tblSpeakers (Speaker_XML)
--PATH
CREATE XML INDEX idx_a 
	ON tblSpeakers (Speaker_XML)
USING XML INDEX idx_1 FOR PATH 

--Insert data into our table
Insert into tblSpeakers Values('Stephen Forte', 'USA',
'
<classes>
	<class name="Writing Secure Code for ASP .NET "/>
	<class name="Using XQuery to Query and Manipulate XML Data in SQL Server 2005"/>
	<class name="SQL Server and Oracle Working Together"/>
	<class name="Protecting against SQL Injection Attacks "/>
</classes>
'
)

Insert into tblSpeakers Values('Richard Campbell', 'Canada',
'
<classes>
	<class name="SQL Server Profiler"/>
	<class name="Advanced SQL Querying Techniques"/>
	<class name="SQL Server and Oracle Working Together"/>
	<class name="T-SQL Error Handling in Yukon"/>
</classes>
'
)

Insert into tblSpeakers Values('Tim Davis', 'USA',
'
<classes>
	<class name="Smart Client Stuff"/>
	<class name="More Smart Client Stuff"/>
</classes>
'
)

Insert into tblSpeakers Values('Malek Kemmou', 'Morocco',
'
<classes>
	<class name="SmartPhone 2005"/>
	<class name="Office System 2003"/>
</classes>
'
)

Insert into tblSpeakers Values('Goksin Bakir', 'Turkey',
'
<classes>
	<class name="SmartPhone 2005"/>
	<class name="Office System 2003"/>
</classes>
'
)

Insert into tblSpeakers Values('Clemens Jones', 'Germany',
'
<classes>
	<class name="SOA"/>
	<class name="FABRIQ"/>
</classes>
'
)

Insert into tblSpeakers Values('Kimberly Smith', 'USA',
'
<classes>
	<class name="SQL Server Index"/>
	<class name="SQL Precon"/>
</classes>
'
)

Insert into tblSpeakers Values('Bad Speaker', 'France',
'
<leclasse>
	<class name="SQL Server Index"/>
	<class name="SQL Precon"/>
</leclasse>
'
)

--xml.exists
DECLARE @XML xml
Set @XML='
<classes>
	<class name="SQL Server Index"/>
	<class name="SQL Precon"/>
</classes>
'

Select @XML.exist('/classes')

--UDF to Determine if an Order XML element exists
--Used for a Check Constraint
USE AdventureWorks
GO
CREATE FUNCTION dbo.DoesOrderXMLDataExist         
(@XML XML)                     
RETURNS bit                        
AS
BEGIN                               
RETURN @XML.exist('/Orders')    
END;
GO

--create the table using the function
CREATE TABLE OrdersXML 
	(OrderDocID INT PRIMARY KEY, 
	xOrders XML NOT NULL Default '<Orders/>'
 CONSTRAINT xml_orderconstraint
  CHECK(dbo.DoesOrderXMLDataExist(xOrders)=1))


--xml.value
USE AdventureWorks
select Speaker_ID, Speaker_NM,  
Speaker_Country, 
	Speaker_XML.value('/classes[1]/class[1]/@name', 'varchar(40)') as Sessions 
From tblSpeakers 
where speaker_country ='USA'

--xml.query
--returns XML data type
--same as previous example but returns XML
select Speaker_ID, Speaker_NM,  
Speaker_Country, 
	Speaker_XML.query('/classes[1]/class[1]') as Sessions 
From tblSpeakers 
where speaker_country ='USA'

--same as previous but returns all
select Speaker_ID, Speaker_NM,  
Speaker_Country, 
	Speaker_XML.query('/classes/class') as Sessions 
From tblSpeakers 
where speaker_country ='USA'

--xml.query using FLOWR
Select Speaker_ID, Speaker_NM, Speaker_Country, Speaker_XML.query('
	for $b in /classes/class
	return ($b)
	') As Sessions
From tblSpeakers

--TSQL where clause and XQuery (exist) Where Clause
Select Speaker_ID, Speaker_NM, Speaker_Country, Speaker_XML.query('/classes/class') As Sessions
From tblSpeakers
Where Speaker_Country='USA' 
and Speaker_XML.exist('/classes/class[@name="SQL Server and Oracle Working Together"]')=1

--xml.nodes()
declare @xml xml
set @xml='<Order>Order1</Order>
				<Order>Order2</Order>'

select T.ref.value('.', 'nvarchar(50)')
from 
(
      select [Xml]=@xml.query('
			for $count in data(/Order) return 
			element temp { $count }
		')
) dat
cross apply dat.xml.nodes('/temp') T(ref)

--sql:column
Select Speaker_ID, Speaker_NM, Speaker_Country, Speaker_XML.query('
	for $b in /classes/class
	where $b/@name="SQL Server and Oracle Working Together"
	return (<Sessions>{$b}<Speaker id="{sql:column("Speaker_ID")}">
		{sql:column("Speaker_NM")}</Speaker></Sessions>)
	') As Sessions
From tblSpeakers
Where Speaker_XML.exist('/classes/class[@name="SQL Server and Oracle Working Together"]')=1

--sql:variable
Declare @Conf varchar(20)
Set @Conf= 'NYC .NET User Group'

Select Speaker_ID, Speaker_NM, Speaker_XML.query('
	for $b in /classes/class
	where $b/@name="SQL Server and Oracle Working Together"
		return (<Sessions 
		conference="{sql:variable("@Conf")}">
		{$b}<Speaker id="{sql:column("Speaker_ID")}">
			{sql:column("Speaker_NM")}</Speaker></Sessions>)
	') As Sessions
From tblSpeakers
Where  Speaker_XML.exist('/classes/class[@name="SQL Server and Oracle Working Together"]')=1

--insert an XML node
Update tblSpeakers
Set Speaker_XML.modify(
'insert 
<class name="Ranking and Windowing Functions in SQL Server 2005"/>
into /classes[1]'
)
Where Speaker_ID=1

--modify
Update tblSpeakers
Set Speaker_XML.modify('delete /classes/class[3]')
Where Speaker_ID=1

--modify a value
Update tblSpeakers
Set Speaker_XML.modify('
	replace value of
	 /classes/class/@name[3]
	 with "Protecting against SQL Injection Attacks-Hackers must die"')
Where Speaker_ID=1
--Select * from tblSpeakers where speaker_id=1

--XML conversion
--insert some data as txt but is XML
--remember no cool XQuery or anything allowed!
DECLARE @xmlData AS varchar(8000)
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<5
Order by Customer.CustomerID
For XML AUTO, Root ('Orders'))
--first insert into the table
Insert Into tblXMLUpgradeTest (Field_XMLData) Values (@xmlData)
--first alter the table
ALTER TABLE tblXMLUpgradeTest ADD Field_XMLData_Temp varchar(8000)
go
--second update the Field_XMLData_Temp column
Update tblXMLUpgradeTest set Field_XMLData_Temp=Field_XMLData
go
--third drop the original text column (Field_XMLData)
ALTER TABLE tblXMLUpgradeTest Drop Column Field_XMLData
go
--fourth add the same column(Field_XMLData) but as XML
ALTER TABLE tblXMLUpgradeTest ADD Field_XMLData XML
go
--fifth insert the XML into Field_XMLData and convert to XML data type
Update tblXMLUpgradeTest set Field_XMLData=Convert(XML, Field_XMLData_Temp)
go
--sixth drop the Field_XMLData_Temp column since it has text, not XML
ALTER TABLE tblXMLUpgradeTest Drop Column Field_XMLData_Temp
go