-- 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'