Transitioning from site specific prototypes to more standardized products for delivery to multiple customer sites has been a driving factor for separating custom source system translations from the product itself.

This has led to many of our products having a standard staging table structure that a custom site-specific solution would feed.

As we begin to interface with more and more source systems the way we get access to that data could take many forms, whether it be an open data connection via a linked server, a flat file, or a messaging service.

Recently we were asked to supply a data requirements document for a TCP/IP eventing service interface and I was looking for an easy way to build an XSD document from our standard STAGE structures.

Queue sirens…. SQL Xml to the rescue.

For our example let’s assume we have two associated stage tables that we want to build an XSD for.

Here is the creation script.

USE tempdb
GO

CREATE SCHEMA STAGE AUTHORIZATION dbo
GO

CREATE TABLE STAGE.CustomerOrderSummary
( [CustomerOrderIdentifier] VARCHAR(32) NOT NULL,
[OrderPriorityCode] VARCHAR(10) NULL,
[CustomerNameAbbv] VARCHAR(6) NULL,
[DateProcessingStarted] DATETIME NULL,
[DateProcessingCompleted] DATETIME NULL,
[DateOrderCompleted] DATETIME NULL,
[DateOrderCancelled] DATETIME NULL,
[OrderStatus] VARCHAR(100) NULL,
[DateOfLastChange] DATETIME NULL,
CONSTRAINT PK_CustomerOrderSummary PRIMARY KEY CLUSTERED ( [CustomerOrderIdentifier] )
)

CREATE TABLE STAGE.CustomerOrderDetail
( [CustomerOrderIdentifier] VARCHAR(32) NOT NULL,
[CustomerDetailIdentifier] VARCHAR(32) NOT NULL,
[ProductName] VARCHAR(150) NULL,
[QuantityOrdered] SMALLINT NULL,
[AutoPickFlag] BIT NULL,
[DateAddedToOrder] DATETIME NULL,
[DatePickStarted] DATETIME NULL,
[DatePickCompleted] DATETIME NULL,
[DateReadyForPickup] DATETIME NULL,
[DatePickCancelled] DATETIME NULL,
[PickStatus] VARCHAR(100) NULL,
[LastKnownLocation] VARCHAR(50) NULL,
[PickLocation] VARCHAR(20) NULL,
[DateOfLastChange] DATETIME NULL,
CONSTRAINT PK_CustomerOrderDetail PRIMARY KEY CLUSTERED ( [CustomerOrderIdentifier], [CustomerDetailIdentifier] )
)

Now to build an XSD from those structures we merely need to use the FOR XML AUTO, ELEMENTS, XMLSCHEMA  option.

DECLARE @XsdSchema xml
SET @XsdSchema = ( SELECT *
FROM STAGE.CustomerOrderSummary
JOIN STAGE.CustomerOrderDetail ON STAGE.CustomerOrderSummary.CustomerOrderIdentifier = STAGE.CustomerOrderDetail.CustomerOrderIdentifier
FOR XML AUTO, ELEMENTS, XMLSCHEMA(‘CustomerOrderProcessing’))
SELECT @XsdSchema

Running your query will generate a result with your generated xml.

Clicking on the xml will open your XSD definition in a new query window where you can save it for inclusion in your interface documentation.

Have you found this useful or found any other interface documentation tricks?

Let’s discuss in the comments.