How to create tables in a database using an XSD schema and SQLXMLBulkload


This question came up many times in the xml newsgroup and forum so I thought I could provide a simple solution to it.


Basically, the user has an XSD schema file and wants to create tables in a database that would correspond to the schema definition.In order to accomplish this, the user needs to annotate the schema file using the SQLXML annotations (see http://msdn2.microsoft.com/en-us/library/ms172649(SQL.90).aspx . By default, complexType elements map to tables and attributes and simpleType elements map to columns).


Bulkload's SchemaGen functionality allows the user to create and drop tables via an API setting.If SchemaGen property is set to TRUE, the tables identified in the schema will be created (the database must exist).If SGDropTables property is also set to TRUE, the tables will be deleted (if previously exist in the database) before they are re-created.


If no data needs to be uploaded (only tables generated), the Bulkload property should be set to FALSE. 


 Below is a small example on how this works.The data file is empty. 


set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")
objBL.ErrorLogFile = "error.xml"


objBL.ConnectionString = "provider=sqloledb;server=myserver;database=tempdb;Integrated Security=SSPI"
objBL.SchemaGen = true
objBL.SGDropTables = true
objBL.Bulkload = false 


objBL.Execute "schema.xml","data.xml"


set objBL=Nothing


Here is the schema file content:


<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
       <xs:element  name="Product" sql:relation="ProductDescription">
        <xs:complexType>
         <xs:sequence>
          <xs:element name="ProductID" type="xs:unsignedInt" sql:field="ProductID" />
          <xs:element name="ProductName" type="xs:string" sql:field="ProductName" />
          <xs:element  name="Description" type="xs:string" sql:field="DescriptionPhraseID" />
         </xs:sequence>
        </xs:complexType>
       </xs:element>
</xs:schema>


 The table that was created in tempdb database:


CREATE TABLE [dbo].[ProductDescription]([ProductID] [int] NULL,


[ProductName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL


[DescriptionPhraseID] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL


) ON [PRIMARY]


 


 One thing to note here is that SchemaGen does not use XSD schema facets and extensions to generate the relational SQL Server schema.It only provides basic functionality and the user should modify the generated tables manually, if needed.


 


Comments (3)
  1. Apple Safari for Windows and Microsoft Silverlight [Via: interactive ] Refactoring Dumb, Dumber, Dumbest…

  2. Duh says:

    We want this without any effort. I have to do this for more than 50 xsd's. Does someone know a tool which generates SQL schema's on feeding xsd's?

  3. Alex says:

    Here's the tool that might be helpful

    github.com/…/xsd2sql

Comments are closed.

Skip to main content