Automatic creation of xml schema (xsd) for a sql server table

At times I need to create an xml schema for a table in the db, such as when exposing an xml interface sproc. I usually lean on the built-in functionality of sql server for generating these; check out the following code.

–Create an example table

create table dbo.Person (PersonId int identity primary key, FirstName nvarchar(100) not null, LastName nvarchar(100) not null, Token uniqueidentifier)

--Generate an xsd under the namespace of myTargetNamespace. If you don't specify a namespace, you will get an autonomically increasing namespace. I find this of little value, so I prefer to choose something specific.

select top 0 * from dbo.Person as Person for xml auto, xmlschema('myTargetNamespace')

--Or you can add the elements directive, if you prefer element rather than attribute centric.

select top 0 * from dbo.Person as Person for xml auto, elements, xmlschema('myTargetNamespace')

--Perhaps you would prefer that the schema was under no namespace? You can retrieve this schema to an xml variable, and then use xml dml to remove the targetNamespace attribute.

declare @myXsd xml
set @myXsd = (select top 0 * from dbo.Person as Person for xml auto, elements, xmlschema('myTargetNamespace'))
set @myXsd.modify('delete /xs:schema[1]/@targetNamespace')
select @myXsd

--ok, now the schema is looking pretty good. Let's pop it into a schema collection (we could do so with the @myXsd above directly, but I'll put the text here so you can see it)

create xml schema collection personXsd  as '<xsd:schema xmlns:schema="myTargetNamespace" xmlns:xsd="<a href="http://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema</a>" xmlns:sqltypes="<a href="http://schemas.microsoft.com/sqlserver/2004/sqltypes">http://schemas.microsoft.com/sqlserver/2004/sqltypes</a>" elementFormDefault="qualified">
<xsd:import namespace="<a href="http://schemas.microsoft.com/sqlserver/2004/sqltypes">http://schemas.microsoft.com/sqlserver/2004/sqltypes</a>" schemaLocation="<a href="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd">http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd</a>" />
<xsd:element name="Person">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="PersonId" type="sqltypes:int" />
<xsd:element name="FirstName">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="100" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="LastName">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="100" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="Token" type="sqltypes:uniqueidentifier" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'

--and here is an example xml instance

declare @person xml(personXsd)
set @person = '<Person>
<PersonId>1</PersonId>
<FirstName>hains</FirstName>
<LastName>tech</LastName>
<Token>47B9897B-FEEC-48D6-8752-ED6074945A6E</Token>
</Person>'
About these ads

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: