You have a table and you would like to create a XSD schema based on that table. What is the easiest way to do that in SQL Server? The easiest way to do that would be to use FOR XML syntax with AUTO, ELEMENTS and XMLSCHEMA. If your table is named test and you want your schema to be named TestXsdSchema then you would do the following

SELECT * FROM Test FOR XML AUTO, ELEMENTS, XMLSCHEMA('TestXsdSchema')

Let’s look at a complete example. First create the table below

create table Test(id int identity,
SomeName varchar(53) not null,
SomeValue decimal(20,10) not null,
SomeGuid uniqueidentifier not null default newsequentialid())

Now execute the following block of code

DECLARE @XsdSchema xml
SET @XsdSchema = (SELECT * FROM Test FOR XML AUTO, ELEMENTS, XMLSCHEMA('TestXsdSchema'))
SELECT @XsdSchema

This is the schema that gets generated

<xsd:schema targetNamespace="TestXsdSchema" elementFormDefault="qualified">
 <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:element name="Test">
<xsd:complexType>
<xsd:sequence>
 <xsd:element name="id" type="sqltypes:int"/>
<xsd:element name="SomeName">
<xsd:simpleType>
<xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
 <xsd:maxLength value="53"/>
 </xsd:restriction>
 </xsd:simpleType>
 </xsd:element>
<xsd:element name="SomeValue">
<xsd:simpleType>
<xsd:restriction base="sqltypes:decimal">
 <xsd:totalDigits value="20"/>
 <xsd:fractionDigits value="10"/>
 </xsd:restriction>
 </xsd:simpleType>
 </xsd:element>
 <xsd:element name="SomeGuid" type="sqltypes:uniqueidentifier"/>
 </xsd:sequence>
 </xsd:complexType>
 </xsd:element>
 </xsd:schema>

See, that was pretty simple wasn’t it?

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum