Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Creating A XSD Schema From A Table In SQL Server With FOR XML Syntax

From Wiki

Jump to: navigation, search

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

tsqlLine number Off | Hide | Select all
  1. SELECT * FROM Test FOR XML AUTO, ELEMENTS, XMLSCHEMA('TestXsdSchema')


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

tsqlLine number Off | Hide | Select all
  1. CREATE TABLE Test(id INT IDENTITY,
  2. SomeName VARCHAR(53) not null,
  3. SomeValue DECIMAL(20,10) not null,
  4. SomeGuid UNIQUEIDENTIFIER not null DEFAULT newsequentialid())

Now execute the following block of code

tsqlLine number Off | Hide | Select all
  1. DECLARE @XsdSchema xml
  2. SET @XsdSchema = (SELECT * FROM Test FOR XML AUTO, ELEMENTS, XMLSCHEMA('TestXsdSchema'))
  3. SELECT @XsdSchema

This is the schema that gets generated

xmlLine number Off | Hide | Select all
  1. <xsd:schema targetNamespace="TestXsdSchema" elementFormDefault="qualified">
  2.  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
  3. <xsd:element name="Test">
  4. <xsd:complexType>
  5. <xsd:sequence>
  6.  <xsd:element name="id" type="sqltypes:int"/>
  7. <xsd:element name="SomeName">
  8. <xsd:simpleType>
  9. <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
  10.  <xsd:maxLength value="53"/>
  11.  </xsd:restriction>
  12.  </xsd:simpleType>
  13.  </xsd:element>
  14. <xsd:element name="SomeValue">
  15. <xsd:simpleType>
  16. <xsd:restriction base="sqltypes:decimal">
  17.  <xsd:totalDigits value="20"/>
  18.  <xsd:fractionDigits value="10"/>
  19.  </xsd:restriction>
  20.  </xsd:simpleType>
  21.  </xsd:element>
  22.  <xsd:element name="SomeGuid" type="sqltypes:uniqueidentifier"/>
  23.  </xsd:sequence>
  24.  </xsd:complexType>
  25.  </xsd:element>
  26.  </xsd:schema>

See, that was pretty simple wasn't it?


Contributed by: --SQLDenis 19:21, 11 May 2009 (GMT)--

Part of SQL Server Programming Hacks

Section XML

644 Rating: 3.0/5 (3 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Creating_A_XSD_Schema_From_A_Table_In_SQL_Server_With_FOR_XML_Syntax"

Powered by MediaWiki GNU Free Documentation License 1.2
Valid XHTML | Valid CSS