using xml to create table in sqlserver



I am tasked with converting an entire Oracle database creation process involving XML and XRD to SQLServer (2008 R2).


MSSSMA for Oracle fails to do the final data migration, although it creates the SS database and tables. I've given up on that.


I managed to convert a subset of the Oracle process to MySQL, the XRD & XML scripts.


It is these I'd like to convert to be able to create and populate the tables separately in the SQLServer database.


May I submit a sample XML for the Oracle DB creation here:



<?xml version="1.0" encoding="UTF-8"?>
-<SqlRepositoryConfig>
<Tablename>EDS_Division</Tablename>
<PrimaryKey>xgenkey</PrimaryKey>
<XGenMsgDBField Format="standard" Type="BINARY">XGEN</XGenMsgDBField>
<CreateStatement> CREATE TABLE EDS_DIVISION ( XGENKEY VARCHAR(36), DIVISION VARCHAR(32), DIVISIONNAME VARCHAR(80), LASTUSER VARCHAR(16), LASTUPDATE DATE, ME_LOCKID VARCHAR(100), ME_LOCKEXPIRE DATE, ME_LOCKINFO VARCHAR(32), XGEN BLOB, PRIMARY KEY(XGENKEY) ) </CreateStatement>
<!-- BEGIN SEQUENCES -->
<CreateStatement> CREATE INDEX EDS_DIVISION_IDX on EDS_Division( DIVISION ) </CreateStatement>
<!-- END SEQUENCES -->
<!-- BEGIN FIELDMAPPINGS -->
-<FieldMapping>
<XGen>/division</XGen>
<DB>DIVISION</DB>
</FieldMapping>
-<FieldMapping>
<XGen>/divisionName</XGen>
<DB>DIVISIONNAME</DB>
</FieldMapping>
-<FieldMapping>
<XGen>/LastUser</XGen>
<DB>LASTUSER</DB>
</FieldMapping>
-<FieldMapping>
<XGen>/LastUpdate</XGen>
<DB>LASTUPDATE</DB>
</FieldMapping>
<!-- END FIELDMAPPINGS -->
</SqlRepositoryConfig>


My simplistic approach would be just to substitute SQL Server CREATE TABLE syntax for Oracle. As I did for MySQL. Is that valid as far as it goes please?


I'd really like to test that and cannot find the XML to just create a SQL Server table.


Regards, Martin


No comments:

Post a Comment