Technical Article

Script SQL Table Schema as XML-XSD

,

I was creating a Proof of Concept (POC) for using Service Broker (SB) to synchronize databases across an untrusted domain boundry. I wanted to validate my XML SB messages using an XSD based on the table definition. I first approached the problem from the standpoint of using VS2008 database edition, but then I found a simpler method by using the SELECT statement with the FOR XML clause. By adding the XMLSCHEMA qualifier the XML Schema definition is added to the payload, but because that's all I want I simply use the TOP 0 clause to suppress any actual data.

Select Top 0
ApplicationId, UserId, Email
FROM dbo.Membership
FOR XML AUTO, XMLSCHEMA

/*** Results ***/<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" 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="dbo.Membership">
 <xsd:complexType>
 <xsd:attribute name="ApplicationId" type="sqltypes:uniqueidentifier" use="required" />
 <xsd:attribute name="UserId" type="sqltypes:uniqueidentifier" use="required" />
 <xsd:attribute name="Email">
 <xsd:simpleType>
 <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
 <xsd:maxLength value="256" />
 </xsd:restriction>
 </xsd:simpleType>
 </xsd:attribute>
 </xsd:complexType>
 </xsd:element>
</xsd:schema>

/*** Table Schema ***/CREATE TABLE [dbo].[Membership](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [Email] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Membership] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO

ALTER TABLE [dbo].[Membership] WITH CHECK ADD CONSTRAINT [FK__aspnet_Me__UserI__0F975522] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO

ALTER TABLE [dbo].[Membership] CHECK CONSTRAINT [FK__aspnet_Me__UserI__0F975522]
GO

Rate

4.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating