use RelXmlDemo
go

if exists (select * from sys.tables where [name] = 'LineItems') drop table dbo.LineItems;
go

create table dbo.LineItems (
	OrderID int not null,
	LineNumber int not null,
	ProductID int not null,
	Quantity int not null default 1,
	UnitPrice money not null,
	-- *****
	ProductExtendedOptions xml null
	-- *****
	constraint PK_LineItems primary key (OrderID, LineNumber)
	-- Order, Product, and Customer IDs reference those other tables respectively...
);
go





--------------------------------------------------
-- Is this okay?

insert	dbo.LineItems
values	( 4832, 3, 927, 1, 49.99,
		  '<ProductConfig>
				<ShoeSize>12</ShoeSize>
				<Colors>
					<Color>Blue</Color>
					<Color>White</Color>
				</Colors>
				<Embroidery>I Love Mom</Embroidery>
				<CustomLaces>Mountain Man #4</CustomLaces>
				<Gift>no</Gift>
			</ProductConfig>')
;


-- What is the TYPE of [ProductExtendedOptions]?
-- XML?




-- Would this be okay?
insert	dbo.LineItems
values	( 4832, 4, 927, 1, 49.99,
		  '<CustomerComplaint>
			<Date>2008-10-10</Date>
			<Subject>Refunds take too long.</Subject>
			<Customer ID="543">Joe Q. Public</Customer>
			<Body>I, Joe, wish to express my heartfelt dissatisfaction about the order I made. The shoes were entirely unacceptable. And getting a refund was like pulling teeth, except not near as expedient.</Body>
		   </CustomerComplaint>'
		 )
;

select * from dbo.LineItems;







--------------------------------------------------
-- Dealing with XML is like STRING.
-- Just "any old xml value" is not okay usually.
-- Column-level check constraints to the rescue.
--------------------------------------------------

if exists (select * from sys.xml_schema_collections where [name] = 'ProductConfig')
	drop xml schema collection ProductConfig;
go

create xml schema collection ProductConfig as
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
	<xs:element name="ProductConfig">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="ShoeSize" minOccurs="0"/>
				<xs:element name="Colors" minOccurs="0">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="Color" maxOccurs="unbounded"/>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
				<xs:element name="Embroidery" minOccurs="0"/>
				<xs:element name="CustomLaces" minOccurs="0"/>
				<xs:element name="Gift" minOccurs="0"/>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>
';




alter table dbo.LineItems
	alter column ProductExtendedOptions xml (ProductConfig) null
;
-- Fails because the old bad data is there!


-- Try again:
truncate table dbo.LineItems;
-- (re-run ALTER)
-- (re-run inserts above)




--------------------------------------------------
-- SEE ALSO Person.Contact in AdventureWorks.
-- The [AdditionalContactInfo] field is strongly-typed XML.
-- If data-management operations never drill into the details
-- of that, say using XQuery, then nothing is "non-relational"
-- about it at all.
--------------------------------------------------

