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

Read 1,965 times
(7 in last 30 days)

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