?? ch 22 - xml & web.sql
字號:
-- Chapter 22 Web Publishing and XML
-------------------------------------------
-- Generating XML from SQL Server
USE CHA2
SELECT Tour.Name, Event.Code, Event.DateBegin
FROM Tour
JOIN Event
ON Tour.TourID = Event.TourID
FOR XML AUTO
SELECT Tour.Name, Event.Code, Event.DateBegin
FROM Tour
JOIN Event
ON Tour.TourID = Event.TourID
FOR XML RAW
SELECT Tour.Name, Event.Code, Event.DateBegin
FROM Tour
JOIN Event
ON Tour.TourID = Event.TourID
FOR XML AUTO, ELEMENTS
---------------------------------------------------
-- Reading XML into SQL Server
DECLARE
@iDOM int,
@XML VarChar(8000)
Set @XML = '
<?xml version="1.0" encoding="UTF-8"?>
<Tours>
<Tour Name="Amazon Trek">
<Event Code="01-003" DateBegin="2001-03-16T00:00:00"/>
<Event Code="01-015" DateBegin="2001-11-05T00:00:00"/>
</Tour>
<Tour Name="Appalachian Trail">
<Event Code="01-005" DateBegin="2001-06-25T00:00:00"/>
<Event Code="01-008" DateBegin="2001-07-14T00:00:00"/>
<Event Code="01-010" DateBegin="2001-08-14T00:00:00"/>
</Tour>
<Tour Name="Bahamas Dive">
<Event Code="01-002" DateBegin="2001-05-09T00:00:00"/>
<Event Code="01-006" DateBegin="2001-07-03T00:00:00"/>
<Event Code="01-009" DateBegin="2001-08-12T00:00:00"/>
</Tour>
<Tour Name="Gauley River Rafting">
<Event Code="01-012" DateBegin="2001-09-14T00:00:00"/>
<Event Code="01-013" DateBegin="2001-09-15T00:00:00"/>
</Tour>
<Tour Name="Outer Banks Lighthouses">
<Event Code="01-001" DateBegin="2001-02-02T00:00:00"/>
<Event Code="01-004" DateBegin="2001-06-06T00:00:00"/>
<Event Code="01-007" DateBegin="2001-07-03T00:00:00"/>
<Event Code="01-011" DateBegin="2001-08-17T00:00:00"/>
<Event Code="01-014" DateBegin="2001-10-03T00:00:00"/>
<Event Code="01-016" DateBegin="2001-11-16T00:00:00"/>
</Tour>
</Tours>'
-- Generate the internal DOM
EXEC sp_xml_preparedocument @iDOM OUTPUT, @XML
-- OPENXML provider.
SELECT *
FROM OPENXML (@iDOM, '/Tours/Tour/Event',8)
WITH ([Name] VARCHAR(25) '../@Name',
Code VARCHAR(10),
DateBegin DATETIME
)
EXEC sp_xml_removedocument @iDOM
-----------------------------------------
-- Web Assistant Code
EXECUTE sp_makewebtask
@outputfile = N'C:\SQLServerBible\Sample Databases
\CapeHatterasAdventures\Events.htm',
@query=N'SELECT Tour.Name, Event.Code, Event.DateBegin
FROM Tour
JOIN Event
ON Tour.TourID = Event.TourID',
@fixedfont=0,
@HTMLheader=3,
@webpagetitle=N'Cape Hatteras Adventures',
@resultstitle=N'Tour Dates',
@URL=N'http://www.SQLServerBible.com',
@reftext=N'www.SQLServerBible.com',
@dbname=N'CHA2',
@whentype=1,
@procname=N'CHA2 Web Page',
@codepage=65001,@charset=N'utf-8'
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -