Here is what we did in class. Remember that we used Visual Studio to set up the xml and the CreateSchema command from the xml menu in Visual Studio to create the schema
Select * From Meeting
Select * From Person for xml raw('person'), elements, root('Persons')
Select lastname, firstname, LicenseNumber, VehicleMake, VehicleYear
From Person
inner Join Customer.vehicle
on person.Personkey=Customer.Vehicle.PersonKey
For Xml auto, elements, root('customers')
Create xml Schema Collection meetingSchema
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://www.automart.com/meeting"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="meeting">
<xs:complexType>
<xs:sequence>
<xs:element name="attending">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="name" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="body">
<xs:complexType>
<xs:sequence>
<xs:element name="topics">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="topic" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="actionItems">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="item" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Create table Meeting
(
meetingID int identity(1,1) primary key,
meetingDate date,
meetingTopic nvarchar(255),
meetingNotes xml(meetingSchema)
)
Insert into Meeting (meetingDate, meetingTopic, meetingNotes)
values (GETDATE(), 'xml stuff',
'<?xml version="1.0" encoding="utf-8"?>
<meeting xmlns="http://www.automart.com/meeting">
<Attending>
<name></name>
<name></name>
</Attending>
<body>
<topics>
<topic></topic>
<topic></topic>
</topics>
<actionItems>
<item></item>
<item></item>
</actionItems>
</body>
</meeting>')
Here is an early one from last year. It may give you more context
Use MagazineSubscription
Select * from Magazine
For xml raw('magazine'), root('Magazines'), Elements
Create xml schema collection MemoCollection
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://www.spconger.com/memo"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="memo">
<xs:complexType>
<xs:sequence>
<xs:element name="heading">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string" />
<xs:element name="from" type="xs:string" />
<xs:element name="about" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="body">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="p" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Create table Memo
(
memoID int identity(1,1) primary Key,
memoDate datetime,
memocontent xml(dbo.MemoCollection)
)
Insert into Memo (memoDate, memocontent)
Values(GETDATE(),
'<?xml version="1.0" encoding="utf-8"?>
<memo xmlns="http://www.spconger.com/memo">
<heading>
<to>Everyone</to>
<from>me</from>
<about>nothing</about>
</heading>
<body>
<p>We need to do nothing today</p>
<p>The same for tomorrow</p>
</body>
</memo>')
Select * from Memo
--administrative sql
Go
Create schema SalesPerson
Create view SalesPerson.vw_Customers
As
Select * from Customer
go
Create login Sales with password='p@ssww0rd1'
Create user Salesuser for login Sales with default_schema=SalesPerson
Grant select on Schema::SalesPerson to SalesUser
No comments:
Post a Comment