
USE xmldb
GO

CREATE XML SCHEMA COLLECTION peoplecoll AS
' <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <!-- other types omitted -->
   <xsd:simpleType name="personAge" >
     <xsd:restriction base="xsd:float" >
       <xsd:maxInclusive value="120" />
       <xsd:minExclusive value="0" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="age" type="personAge" />
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:tns=c
            targetNamespace="http://example.org/LogansRun" >
   <!-- other types omitted -->
   <xsd:simpleType name="personAge" >
     <xsd:restriction base="xsd:float" >
       <xsd:maxInclusive value="30" />
       <xsd:minExclusive value="0" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="age" type="tns:personAge" />
</xsd:schema>'
GO


CREATE TABLE person_tab(
	id INT IDENTITY PRIMARY KEY,
	-- the person column can only contain
	-- infoset items that are defined in the schema collection
	-- defined above
	person XML(peoplecoll))
GO

-- this works, person between 0 and 30 years old
INSERT INTO person_tab VALUES(
'<age xmlns="http://example.org/LogansRun">11</age>')
GO

-- so does this, using the no-namespace schema
INSERT INTO person_tab VALUES(
'<age>75</age>')
GO

-- this insert fails
INSERT INTO person_tab VALUES(
'<age xmlns="http://example.org/LogansRun">31</age>')
GO


CREATE TABLE person_docs(
	id INT IDENTITY primary key,
	person XML(DOCUMENT peoplecoll))
GO

CREATE TABLE person_content(
	id INT IDENTITY PRIMARY KEY,
	person XML(CONTENT peoplecoll))
GO

-- this works with either table, a single root element
INSERT INTO person_docs VALUES(
'<age xmlns="http://example.org/LogansRun">11</age>')

INSERT INTO person_content VALUES(
'<age xmlns="http://example.org/LogansRun">11</age>')

-- this fails, more than one root element
INSERT INTO person_docs VALUES(
'<age xmlns="http://example.org/LogansRun">5</age>
<age xmlns="http://example.org/LogansRun">5</age>
')
GO

-- this works because it's a valid fragment
INSERT INTO person_content VALUES(
'<age xmlns="http://example.org/LogansRun">5</age>
<age xmlns="http://example.org/LogansRun">5</age>
')
GO

DECLARE @x XML(accountingcoll)
-- input is validated here
SET @x =
'<po xmlns="urn:com-develop:purchaseorder">
	<orderid>4321</orderid>
	<customerid>10753</customerid>
	<items>
		<itemno>987-65</itemno>
		<qty>5</qty>
	</items>
</po>'