-- XQuery_Composition_XmlSchema_Query
begin
	with	xmlnamespaces
		(
		'http://www.w3.org/2001/XMLSchema' as xs
		)
	,TypeMap	-- Mapping SQL Server data types to XML Schema data types
		(
		SqlType		-- SQL Server Data Type
		,XmlSchemaType	-- XML Schema Data Type
		,IsFixed	-- 1 = dimensions cannot be set, 0 = dimensions should be set
		)
	as
	(
	select	'bigint' as SqlType
		,'xs:long' as XmlSchemaType
		,1 as IsFixed
	union
	select	'binary'
		,'xs:hexBinary'
		,0
	union
	select	'bit'
		,'xs:boolean'
		,1
	union
	select	'char'
		,'xs:string'
		,0
	union
	select	'date'
		,'xs:dateTime'
		,1
	union
	select	'datetime'
		,'xs:dateTime'
		,1
	union
	select	'datetime2'
		,'xs:dateTime'
		,0
	union
	select	'datetimeoffset'
		,'xs:dateTime'
		,0
	union
	select	'decimal'
		,'xs:decimal'
		,0
	union
	select	'float'
		,'xs:double'
		,0
	union
	select	'image'
		,'xs:hexBinary'
		,0
	union
	select	'int'
		,'xs:int'
		,1
	union
	select	'money'
		,'xs:decimal'
		,0
	union
	select	'nchar'
		,'xs:string'
		,0
	union
	select	'ntext'
		,'xs:string'
		,1
	union
	select	'numeric'
		,'xs:decimal'
		,0
	union
	select	'nvarchar'
		,'xs:string'
		,0
	union
	select	'real'
		,'xs:float'
		,0
	union
	select	'rowversion'
		,'xs:hexBinary'
		,1
	union
	select	'smalldatetime'
		,'xs:dateTime'
		,1
	union
	select	'smallint'
		,'xs:short'
		,1
	union
	select	'smallmoney'
		,'xs:decimal'
		,0
	union
	select	'sql_variant'
		,'xs:string'
		,1
	union
	select	'text'
		,'xs:string'
		,1
	union
	select	'time'
		,'xs:string'
		,1
	union
	select	'timestamp'
		,'xs:hexBinary'
		,1
	union
	select	'tinyint'
		,'xs:unsignedByte'
		,1
	union
	select	'uniqueidentifier'
		,'xs:string'
		,1
	union
	select	'varbinary'
		,'xs:hexBinary'
		,0
	union
	select	'varchar'
		,'xs:string'
		,0
	union
	select	'xml'
		,'xs:string'
		,1
	)
	,Nillability	-- Mapping SQL Server nillability to XML Schema nillability
		(
		SqlNillable
		,XmlNillable
		)
	as
	(
	select	'NO' as SqlNillable
		,N'false' as XmlNillable
	union
	select	'YES'
		,N'true'
	)
	,PrimaryKey
	as
	(
	select	TABLE_CONSTRAINTS.TABLE_CATALOG as [Catalog]
		,TABLE_CONSTRAINTS.TABLE_SCHEMA as [Schema]
		,TABLE_CONSTRAINTS.TABLE_NAME as [Table]
		,TABLE_CONSTRAINTS.CONSTRAINT_NAME as Name
		,[Columns]
			= (
			select	COLUMN_NAME as [@xpath]
				from	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
				where	(KEY_COLUMN_USAGE.TABLE_CATALOG = TABLE_CONSTRAINTS.TABLE_CATALOG)
					and (KEY_COLUMN_USAGE.TABLE_SCHEMA = TABLE_CONSTRAINTS.TABLE_SCHEMA)
					and (KEY_COLUMN_USAGE.TABLE_NAME = TABLE_CONSTRAINTS.TABLE_NAME)
					and (KEY_COLUMN_USAGE.CONSTRAINT_NAME =	TABLE_CONSTRAINTS.CONSTRAINT_NAME)
				order by	KEY_COLUMN_USAGE.ORDINAL_POSITION
				for xml path('xs:field'), type
			)
		from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS
		where	(TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY')
	)
	,UniqueConstraint
	as
	(
	select	TABLE_CONSTRAINTS.TABLE_CATALOG as [Catalog]
		,TABLE_CONSTRAINTS.TABLE_SCHEMA as [Schema]
		,TABLE_CONSTRAINTS.TABLE_NAME as [Table]
		,TABLE_CONSTRAINTS.CONSTRAINT_NAME as Name
		,[Columns]
			= (
			select	COLUMN_NAME as [@xpath]
				from	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
				where	(KEY_COLUMN_USAGE.TABLE_CATALOG = TABLE_CONSTRAINTS.TABLE_CATALOG)
					and (KEY_COLUMN_USAGE.TABLE_SCHEMA = TABLE_CONSTRAINTS.TABLE_SCHEMA)
					and (KEY_COLUMN_USAGE.TABLE_NAME = TABLE_CONSTRAINTS.TABLE_NAME)
					and (KEY_COLUMN_USAGE.CONSTRAINT_NAME =	TABLE_CONSTRAINTS.CONSTRAINT_NAME)
				order by	KEY_COLUMN_USAGE.ORDINAL_POSITION
				for xml path('xs:field'), type
			)
		from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS
		where	(TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE')
	)
	,SelfReference
	as
	(
	select	FOREIGN_KEY.TABLE_CATALOG as [ForeignCatalog]
		,FOREIGN_KEY.TABLE_SCHEMA as [ForeignSchema]
		,FOREIGN_KEY.TABLE_NAME as [ForeignTable]
		,FOREIGN_KEY.CONSTRAINT_NAME as ForeignKeyName
		,PRIMARY_KEY.CONSTRAINT_NAME as PrimaryKeyName
		,[ForeignColumns]
			= (
			select	COLUMN_NAME as [@xpath]
				from	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
				where	(KEY_COLUMN_USAGE.TABLE_CATALOG = FOREIGN_KEY.TABLE_CATALOG)
					and (KEY_COLUMN_USAGE.TABLE_SCHEMA = FOREIGN_KEY.TABLE_SCHEMA)
					and (KEY_COLUMN_USAGE.TABLE_NAME = FOREIGN_KEY.TABLE_NAME)
					and (KEY_COLUMN_USAGE.CONSTRAINT_NAME =	FOREIGN_KEY.CONSTRAINT_NAME)
				order by	KEY_COLUMN_USAGE.ORDINAL_POSITION
				for xml path('xs:field'), type
			)
		from	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			inner join	INFORMATION_SCHEMA.TABLE_CONSTRAINTS FOREIGN_KEY
					on	FOREIGN_KEY.CONSTRAINT_NAME = REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
			inner join	INFORMATION_SCHEMA.TABLE_CONSTRAINTS PRIMARY_KEY
					on	PRIMARY_KEY.CONSTRAINT_NAME = REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME
		where	(FOREIGN_KEY.CONSTRAINT_TYPE = 'FOREIGN KEY')
			and (FOREIGN_KEY.TABLE_CATALOG = PRIMARY_KEY.TABLE_CATALOG)
			and (FOREIGN_KEY.TABLE_SCHEMA = PRIMARY_KEY.TABLE_SCHEMA)
			and (FOREIGN_KEY.TABLE_NAME = PRIMARY_KEY.TABLE_NAME)
	)
	select	TABLES.TABLE_CATALOG as [Catalog]
		,TABLES.TABLE_SCHEMA as [Schema]
		,TABLES.TABLE_NAME as [Table]
		,TableXmlSchema
			= (
			select	(-- Table Columns
				select	case
						when	(TypeMap.IsFixed = 1)
							then	cast(N'' as xml).query
									('
									element		xs:element
									{
										attribute	name {sql:column("COLUMNS.COLUMN_NAME")}
										,attribute	type {sql:column("TypeMap.XmlSchemaType")}
										,attribute	nillable {sql:column("Nillability.XmlNillable")}
									}
									')
						when	(COLUMNS.CHARACTER_MAXIMUM_LENGTH is not null)
							and (COLUMNS.CHARACTER_MAXIMUM_LENGTH != -1)
							then	cast(N'' as xml).query
									('
									element		xs:element
									{
										attribute	name {sql:column("COLUMNS.COLUMN_NAME")}
										,attribute	nillable {sql:column("Nillability.XmlNillable")}
										,element	xs:simpleType
										{
											element		xs:restriction
											{
												attribute	base {sql:column("TypeMap.XmlSchemaType")}
												,element	xs:maxLength
												{
													attribute	value {sql:column("COLUMNS.CHARACTER_MAXIMUM_LENGTH")}
												}
											}
										}
									}
									')
						when	(COLUMNS.CHARACTER_MAXIMUM_LENGTH is not null)
							and (COLUMNS.CHARACTER_MAXIMUM_LENGTH = -1)
							then	cast(N'' as xml).query
									('
									element		xs:element
									{
										attribute	name {sql:column("COLUMNS.COLUMN_NAME")}
										,attribute	nillable {sql:column("Nillability.XmlNillable")}
										,element	xs:simpleType
										{
											element		xs:restriction
											{
												attribute	base {sql:column("TypeMap.XmlSchemaType")}
												,element	xs:maxLength
												{
													attribute	value {2147483647}
												}
											}
										}
									}
									')
						when	(COLUMNS.CHARACTER_MAXIMUM_LENGTH is null)
							and (COLUMNS.NUMERIC_PRECISION is not null)
							and (COLUMNS.NUMERIC_SCALE is null or COLUMNS.NUMERIC_SCALE = 0)
							then	cast(N'' as xml).query
									('
									element		xs:element
									{
										attribute	name {sql:column("COLUMNS.COLUMN_NAME")}
										,attribute	nillable {sql:column("Nillability.XmlNillable")}
										,element	xs:simpleType
										{
											element		xs:restriction
											{
												attribute	base {sql:column("TypeMap.XmlSchemaType")}
												,element	xs:totalDigits
												{
													attribute	value {sql:column("COLUMNS.NUMERIC_PRECISION")}
												}
											}
										}
									}
									')
						when	(COLUMNS.CHARACTER_MAXIMUM_LENGTH is null)
							and (COLUMNS.NUMERIC_PRECISION is not null)
							and (COLUMNS.NUMERIC_SCALE is not null)
							and (COLUMNS.NUMERIC_SCALE != 0)
							then	cast(N'' as xml).query
									('
									element		xs:element
									{
										attribute	name {sql:column("COLUMNS.COLUMN_NAME")}
										,attribute	nillable {sql:column("Nillability.XmlNillable")}
										,element	xs:simpleType
										{
											element		xs:restriction
											{
												attribute	base {sql:column("TypeMap.XmlSchemaType")}
												,element	xs:totalDigits
												{
													attribute	value {sql:column("COLUMNS.NUMERIC_PRECISION")}
												}
												,element	xs:fractionDigits
												{
													attribute	value {sql:column("COLUMNS.NUMERIC_SCALE")}
												}
											}
										}
									}
									')
						else	cast(N'' as xml).query
								('
								element		xs:element
								{
									attribute	name {sql:column("COLUMNS.COLUMN_NAME")}
									,attribute	type {"xs:string"}
									,attribute	nillable {sql:column("Nillability.XmlNillable")}
								}
								')
						end
					from	INFORMATION_SCHEMA.COLUMNS
						inner join	Nillability
								on	Nillability.SqlNillable = COLUMNS.IS_NULLABLE
						left join	TypeMap
								on	TypeMap.SqlType = COLUMNS.DATA_TYPE
					where	(COLUMNS.TABLE_CATALOG = TABLES.TABLE_CATALOG)
						and (COLUMNS.TABLE_SCHEMA = TABLES.TABLE_SCHEMA)
						and (COLUMNS.TABLE_NAME = TABLES.TABLE_NAME)
					order by	COLUMNS.ORDINAL_POSITION
					for xml path(''), root('Columns'), type
				)
				,(-- Primary Key
				select	PrimaryKey.Name as [@name]
					,'row' as [xs:selector/@xpath]
					,(
					select	PrimaryKey.[Columns]
					)
					from	PrimaryKey
					where	(PrimaryKey.[Catalog] = TABLES.TABLE_CATALOG)
						and (PrimaryKey.[Schema] = TABLES.TABLE_SCHEMA)
						and (PrimaryKey.[Table] = TABLES.TABLE_NAME)
					for xml path('xs:key'), root('PrimaryKey'), type
				)
				,(-- Unique Constraint(s)
				select	UniqueConstraint.Name as [@name]
					,'row' as [xs:selector/@xpath]
					,(
					select	UniqueConstraint.[Columns]
					)
					from	UniqueConstraint
					where	(UniqueConstraint.[Catalog] = TABLES.TABLE_CATALOG)
						and (UniqueConstraint.[Schema] = TABLES.TABLE_SCHEMA)
						and (UniqueConstraint.[Table] = TABLES.TABLE_NAME)
					for xml path('xs:unique'), root('UniqueConstraints'), type
				)
				,(-- Self-reference Foreign Key(s)
				select	SelfReference.PrimaryKeyName as [@refer]
					,SelfReference.ForeignKeyName as [@name]
					,'row' as [xs:selector/@xpath]
					,(
					select	SelfReference.[ForeignColumns]
					)
					from	SelfReference
					where	(SelfReference.[ForeignCatalog] = TABLES.TABLE_CATALOG)
						and (SelfReference.[ForeignSchema] = TABLES.TABLE_SCHEMA)
						and (SelfReference.[ForeignTable] = TABLES.TABLE_NAME)
					for xml path('xs:keyref'), root('SelfReferences'), type
				)
				for xml path(''), root('Table'), type
			).query
			('
			element		xs:schema
			{
				element		xs:element
				{
					attribute	name {concat(sql:column("TABLES.TABLE_SCHEMA"), ".", sql:column("TABLES.TABLE_NAME"))}
					,element	xs:complexType
					{
						element		xs:choice
						{
							attribute	minOccurs {0}
							,attribute	maxOccurs {"unbounded"}
							,element	xs:element
							{
								attribute	name {"row"}
								,element	xs:complexType
								{
									(: Table Columns :)
									element		xs:sequence {Table/Columns/xs:element}
								}
							}
						}
					}
					(: Primary Key :)
					,Table/PrimaryKey/xs:key
					(: Unique Constraint(s) :)
					,Table/UniqueConstraints/xs:unique
					(: Self-referencing Foreign Key(s) :)
					,Table/SelfReferences/xs:keyref
				}
			}
			')
		from	INFORMATION_SCHEMA.TABLES
	;
end
go