XML FeedsYou 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
tsql
- SELECT * FROM Test FOR XML AUTO, ELEMENTS, XMLSCHEMA('TestXsdSchema')
Let's look at a complete example. First create the table below
tsql
- 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
tsql
- DECLARE @XsdSchema xml
- SET @XsdSchema = (SELECT * FROM Test FOR XML AUTO, ELEMENTS, XMLSCHEMA('TestXsdSchema'))
- SELECT @XsdSchema
This is the schema that gets generated
xml
- <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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Dear Denis, thanks for your example, 
Nice! Helpful.
Helpful. Thanks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.