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

  • Article

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 https://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="https://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.