XSD Generator
This script will generate a stored procedure named procCreateXSD. Run this script to create the SP.
The purpose of this SP is to generate the XSD from selected tables we passed to the script. It accepts the Dataset name, URI, and the list of the tables to be included.
For example:
exec proccreateXSD 'NorthwindDataset', 'http://www.tempuri.org/', 'Customers,Orders,Products'
Be aware that this SP uses system tables that might be changed int the future version of SQL Server.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'procCreateXSD'
AND type = 'P')
DROP PROCEDURE dbo.procCreateXSD
GO
CREATE PROCEDURE dbo.procCreateXSD
@datasetName as varchar(50),
@uri as varchar(500), -- padded with a '/'
@TableList as varchar(8000) -- comma separated table list with no additional space
AS
set nocount on
select a.Name, a.id
into #tableList
from sysobjects a
where a.type='U'
and charindex(','+rtrim(a.name)+',', ','+@tablelist+',')>0
select *
from (
Select 1 as Tag,
null as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
union all
Select 2 as Tag,
1 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
union all
Select 3 as Tag,
2 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
union all
Select 4 as Tag,
3 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
'unbounded' as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
union all
Select 5 as Tag,
4 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
'unbounded' as [xs:choice!4!maxOccurs],
a.Name as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from #tableList a
union all
Select 6 as Tag,
5 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
'unbounded' as [xs:choice!4!maxOccurs],
a.Name as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from #tableList a
union all
Select 7 as Tag,
6 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
'unbounded' as [xs:choice!4!maxOccurs],
a.Name as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from #tableList a
union all
Select 8 as Tag,
7 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
'unbounded' as [xs:choice!4!maxOccurs],
a.Name as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
b.Name as [xs:element!8!name],
case b.xtype
when 127 then 'xs:long'
when 173 then 'xs:base64Binary'
when 104 then 'xs:boolean'
when 175 then 'xs:string'
when 61 then 'xs:dateTime'
when 106 then 'xs:decimal'
when 62 then 'xs:double'
when 34 then 'xs:base64Binary'
when 56 then 'xs:int'
when 60 then 'xs:decimal'
when 239 then 'xs:string'
when 99 then 'xs:string'
when 108 then 'xs:decimal'
when 231 then 'xs:string'
when 59 then 'xs:float'
when 58 then 'xs:dateTime'
when 52 then 'xs:short'
when 122 then 'xs:decimal'
when 98 then 'xs:string'
when 35 then 'xs:string'
when 189 then 'xs:base64Binary'
when 48 then 'xs:unsignedByte'
when 36 then 'xs:string'
when 165 then 'xs:base64Binary'
when 167 then 'xs:string'
else 'xs:string' end as [xs:element!8!type],
case when b.isnullable=0 then null else '0' end as [xs:element!8!minOccurs],
case b.xtype
when 98 then 'System.Object, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
when 36 then 'System.Guid, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
else null end as [xs:element!8!msdata:DataType],
case when b.colstat & 1 = 1
then 'true'
else null end as [xs:element!8!msdata:ReadOnly],
case when b.colstat & 1 = 1
then 'true'
else null end as [xs:element!8!msdata:AutoIncrement],
b.colorder as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from #tableList a, syscolumns b
where b.id=a.id
union all
select 10 as Tag,
2 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
replace(b.Name,'_','') as [xs:unique!10!name],
case when b.status & 2048<>0 then 'true' else null end as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from #tableList a, sysindexes b
where b.id=a.id
and (b.status & 6144)<>0
union all
select 11 as Tag,
10 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
replace(b.Name,'_','') as [xs:unique!10!name],
case when b.status & 2048<>0 then 'true' else null end as [xs:unique!10!msdata:PrimaryKey],
'.//mstns:'+a.Name as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from #tableList a, sysindexes b
where b.id=a.id
and (b.status & 6144)<>0
union all
select 12 as Tag,
10 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
replace(b.Name,'_','') as [xs:unique!10!name],
case when b.status & 2048<>0 then 'true' else null end as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
'mstns:'+d.Name as [xs:field!12!xpath],
c.keyno as [xs:field!12!colorder!hide],
null as [xs:keyref!20!name],
null as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from #tableList a, sysindexes b, sysindexkeys c, syscolumns d
where b.id=a.id
and (b.status & 6144)<>0
and c.id=a.id and c.indid=b.indid and d.id=a.id and d.colid=c.colid
union all
select 20 as Tag,
2 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
replace(e.Name,'_','') as [xs:keyref!20!name],
replace(d.Name,'_','') as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from sysreferences a, #tableList b, #tableList c, sysindexes d, sysobjects e
where b.id=a.rkeyid and c.id=a.fkeyid
and d.id=a.rkeyid and d.indid=a.rkeyindid and e.id=a.constid
union all
select 21 as Tag,
20 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
replace(e.Name,'_','') as [xs:keyref!20!name],
replace(d.Name,'_','') as [xs:keyref!20!refer],
'.//mstns:'+c.name as [xs:selector!21!xpath],
null as [xs:field!22!xpath],
null as [xs:field!22!keyno!hide]
from sysreferences a, #tableList b, #tableList c, sysindexes d, sysobjects e
where b.id=a.rkeyid and c.id=a.fkeyid
and d.id=a.rkeyid and d.indid=a.rkeyindid and e.id=a.constid
union all
select 22 as Tag,
20 as Parent,
@datasetName as [xs:schema!1!id],
@uri+@datasetName+'.xsd' as [xs:schema!1!targetNamespace],
'qualified' as [xs:schema!1!elementFormDefault],
'qualified' as [xs:schema!1!attributeFormDefault],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns],
@uri+@datasetName+'.xsd' as [xs:schema!1!xmlns:mstns],
'http://www.w3.org/2001/XMLSchema' as [xs:schema!1!xmlns:xs],
'urn:schemas-microsoft-com:xml-msdata' as [xs:schema!1!xmlns:msdata],
@datasetName as [xs:element!2!name],
'true' as [xs:element!2!msdata:IsDataSet],
null as [xs:complexType!3!!element],
null as [xs:choice!4!maxOccurs],
null as [xs:element!5!name],
null as [xs:complexType!6!!element],
null as [xs:sequence!7!!element],
null as [xs:element!8!name],
null as [xs:element!8!type],
null as [xs:element!8!minOccurs],
null as [xs:element!8!msdata:DataType],
null as [xs:element!8!msdata:ReadOnly],
null as [xs:element!8!msdata:AutoIncrement],
null as [xs:element!8!colorder!hide],
null as [xs:unique!10!name],
null as [xs:unique!10!msdata:PrimaryKey],
null as [xs:selector!11!xpath],
null as [xs:field!12!xpath],
null as [xs:field!12!colorder!hide],
replace(e.Name,'_','') as [xs:keyref!20!name],
replace(d.Name,'_','') as [xs:keyref!20!refer],
null as [xs:selector!21!xpath],
'mstns:'+g.Name as [xs:field!22!xpath],
f.keyno as [xs:field!22!keyno!hide]
from sysreferences a, #tableList b, #tableList c, sysindexes d, sysobjects e, sysforeignkeys f,syscolumns g
where b.id=a.rkeyid and c.id=a.fkeyid
and d.id=a.fkeyid and d.indid=a.rkeyindid and e.id=a.constid
and f.constid=a.constid and g.id=f.fkeyid and g.colid=f.fkey
) x
order by Tag / 10,
[xs:element!2!name],
[xs:complexType!3!!element],
[xs:element!5!name],
[xs:unique!10!msdata:PrimaryKey],
[xs:unique!10!name],
[xs:keyref!20!name],
Parent,
[xs:element!8!colorder!hide],
[xs:field!12!colorder!hide],
[xs:field!22!keyno!hide]
for xml explicit
GO 
