Remember we made the xml file for the memos in Visual Studio and then used the xml menu build schema option to create the schema.
Here is the Xml Schema collection
Create xml schema collection automartmemos
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="header">
<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:element name="date" 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>'
Now we create a table that has a field that uses the xml data type. We bind that field to the xml schema collection that we have just created.
Create table AutomartMemos
(
AutomartMemoID int identity(1,1) primary key,
MemoDescription nvarchar(255),
Memo xml (dbo.automartmemos)
)
Now we insert into the database. The xml below will fail because the from and about elements are in reverse order. When it matches the structure of the xml document against the schema it will see the difference and throw an error.
Insert into AutomartMemos(MemoDescription, Memo)
Values('Too much of nothing',
'<?xml version="1.0" encoding="utf-8"?>
<memo xmlns="http://www.spconger.com/memo">
<header>
<to>everyone</to>
<about>nothing</about>
<from>me</from>
<date>7/19/2011</date>
</header>
<body>
<p>There is nothing going on today</p>
<p>But we have to appear busy</p>
</body>
</memo>')
Below is an example of xml raw with all the possible options. It is set to provide a root element named persons and a grouping element named person. After the SQL I show a small part of the output.
Select * From Person
for xml raw('person'), root('persons'),elements
<persons>
<person>
<Personkey>1</Personkey>
<LastName>Anderson</LastName>
<FirstName>Jay</FirstName>
</person>
<person>
<Personkey>2</Personkey>
<LastName>Zimmerman</LastName>
<FirstName>Toby</FirstName>
</person>
<person>
<Personkey>3</Personkey>
<LastName>Mann</LastName>
<FirstName>Louis</FirstName>
</person>
. . .
Here is an example of xml auto. It allows for deeper and more complex
hierarchies of xml. I am showing a fragment of the output.
Select LastName, firstname, LicenseNumber, VehicleMake, VehicleYear
From Person
inner join customer.vehicle
on Person.Personkey=Customer.Vehicle.PersonKey
order by Lastname
for xml auto, root('vehicles'), elements
<Person>
<LastName>Fabre</LastName>
<firstname>Jill</firstname>
<customer.vehicle>
<LicenseNumber>LKJ098</LicenseNumber>
<VehicleMake>Ford Freestar</VehicleMake>
<VehicleYear>2004</VehicleYear>
</customer.vehicle>
<customer.vehicle>
<LicenseNumber>OPO234</LicenseNumber>
<VehicleMake>Jeep Cherokee</VehicleMake>
<VehicleYear>1998</VehicleYear>
</customer.vehicle>
</Person>
No comments:
Post a Comment