Use VenueTracker
Select * from Customer
for xml raw
Select * from Customer
for xml raw, elements, root('customers')
Select * from Customer
for xml raw('customer'), elements, root('customers')
Select Distinct CustomerLastName, customerEmail, ArtistName
From Customer cust
Inner join CustomerArtist custartist
on cust.CustomerID=custArtist.ArtistID
inner Join Artist art
on art.ArtistID=custartist.ArtistID
order by CustomerLastName
for xml auto, elements, root ('root')
Select * From customerArtist
Use Automart
Select * From Customer.AutoService
use VenueTracker
Create xml schema collection schema_VenueDescription
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
targetNamespace="http://www.venutracker.com/description"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="venuedescription">
<xs:complexType>
<xs:sequence>
<xs:element name="header">
<xs:complexType>
<xs:sequence>
<xs:element name="datelastupdated" type="xs:string" />
<xs:element name="contactname" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="body">
<xs:complexType>
<xs:sequence>
<xs:element name="description" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Alter table Venue
Drop column VenueDescription
Alter Table Venue
Add VenueDescription Xml(schema_VenueDescription)
Select * From Venue
Update Venue
Set VenueDescription ='<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
<header>
<contactname>John Doe</contactname>
<datelastupdated>2012-7-1</datelastupdated>
</header>
<body>
<description>
The Gorge Amphitheatre is located in the Columbia River in George,
Washington. It offers lawn-terrace seating. Administered by Live Nation,
it is considered one of the premier and most scenic concert locations
not just in North America, but the world.[1]
The venue has been a host to big name performers like The Who,
David Bowie, Coldplay, Tom Petty, Pearl Jam, Dave Matthews Band,
Phish, and Rush. The venue offers sweeping and
majestic views of the Columbia River as it skirts the foothills
of the Cascade Range southbound, as well as extreme eastern Kittitas
County and extreme western Grant County. It is also known for its spectacular
views of the Columbia gorge canyon (not to be confused with the
Columbia River Gorge proper, which begins several tens of miles
further downstream as the river turns west to cross through the
Cascades toward Portland and the Pacific Ocean).
</description>
</body>
</venuedescription>'
Where VenueId=2
Insert into venue( VenueName, VenueAddress1, VenueAddress2, VenueCity, VenueState, VenuePhone, VenueCapacity, VenueAgeRestriction, VenueURL, VenueDescription)
Values ('jazz Alley', null, '2033 6th Ave South', 'Seattle','WA','2065553214', 200,1,'http://jazzalley.com','<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
<header>
<datelastupdated>2012-23-1</datelastupdated>
<contactname>Jane Doe</contactname>
</header>
<body>
<description>
An intimate venue with food and drinks for viewing world renowned artists.
</description>
</body>
</venuedescription>')
use Automart
Select * From customer.AutoService
Select ServiceName, Serviceprice,
ServiceDescription.query('declare namespace d="http://www.automart.com/servicedescription"; d:servicedescription/d:parts/d:part/d:perpartprice') as parts
From Customer.AutoService
Where ServiceName='tune up'
Tuesday, July 24, 2012
XML in SQL Server
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment