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