How to generate an XSD schema - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to generate an XSD schema

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-05-06 : 06:26:37

Hi there

I want to generate an XSD for my database. I have a simple query, but the output is not quite what I want, so I was wondering if there is an easy way to do it?

In the example below, I have a Header table and a Child table (Item). The XSD I want to create will display this relationship (so the child elements will be nested in the Header element), and I also want to remove the fields that have already been declared in the Header elements. For instance, the Item table is related to the header by the TransactionID and EndTransDateTime. I don't want to repeat those elements in the Item XSD:


CREATE TABLE dbo.Header
(
  TransactionID		INT
, EndTransDateTime	DATETIME
, Amount			INT
, CONSTRAINT PK_Header PRIMARY KEY
	(
	  TransactionID
	, EndTransDateTime
	)
)

CREATE TABLE dbo.Item
(
  TransactionID		INT
, EndTransDateTime	DATETIME
, RecordSeqNo		INT
, ProductID			INT
, CONSTRAINT PK_Item PRIMARY KEY
	(
	  TransactionID
	, EndTransDateTime
	, RecordSeqNo
	)
)
ALTER TABLE dbo.Item 
ADD CONSTRAINT FK_Header_Item FOREIGN KEY (TransactionID,EndTransDateTime) REFERENCES dbo.Header(TransactionID,EndTransDateTime)

SELECT		* 
FROM		dbo.Header Header 
INNER JOIN	dbo.Item Item 
		ON	Item.TransactionID = Header.TransactionID
		AND Item.EndTransDateTime = Header.EndTransDateTime
FOR XML AUTO, XMLSCHEMA ('CRDM Schema')

DROP TABLE dbo.Item
DROP TABLE dbo.Header

CURRENT OUTPUT:

<xsd:schema targetNamespace="CRDM Schema" xmlns:schema="CRDM Schema" 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="Header">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element ref="schema:Item" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
      <xsd:attribute name="TransactionID" type="sqltypes:int" use="required" />
      <xsd:attribute name="EndTransDateTime" type="sqltypes:datetime" use="required" />
      <xsd:attribute name="Amount" type="sqltypes:int" />
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="Item">
    <xsd:complexType>
      <xsd:attribute name="TransactionID" type="sqltypes:int" use="required" />
      <xsd:attribute name="EndTransDateTime" type="sqltypes:datetime" use="required" />
      <xsd:attribute name="RecordSeqNo" type="sqltypes:int" use="required" />
      <xsd:attribute name="ProductID" type="sqltypes:int" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

DESIRED OUTPUT:

<xsd:schema targetNamespace="CRDM Schema" xmlns:schema="CRDM Schema" 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="Header">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element ref="schema:Item" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
      <xsd:attribute name="TransactionID" type="sqltypes:int" use="required" />
      <xsd:attribute name="EndTransDateTime" type="sqltypes:datetime" use="required" />
      <xsd:attribute name="Amount" type="sqltypes:int" />
       <xsd:element name="Item">
        <xsd:complexType>
          <xsd:attribute name="RecordSeqNo" type="sqltypes:int" use="required" />
          <xsd:attribute name="ProductID" type="sqltypes:int" />
        </xsd:complexType>
      </xsd:element> 
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The section in red is currently not valid.

Is there an easy way to do this, or a way at all?

Thanks

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources