locked
Convert XML Oracle DDL & run in to SQL Server RRS feed

  • Question

  • Hi

    I need to edit XML files that currently create Oracle and MySQL DDL to T-SQL.

    I've done that simplistically, confirmed by a senior developer, as in the following example and need to test this by running the XML script against a SQL Server 2008 R2 database.  How do I do that please? 

    <?xml version="1.0" encoding="UTF-8"?>
    <SqlRepositoryConfig>
     <Tablename>EDS_Division</Tablename>
     <PrimaryKey>xgenkey</PrimaryKey>
     <XGenMsgDBField Type="BINARY" Format="standard">XGEN</XGenMsgDBField>
     <CreateStatement>
     CREATE TABLE [dbo].[EDS_DIVISION]
     (
     [XGENKEY]       [varchar](36)     NOT NULL,
     [DIVISION]      [varchar](32)     NULL,
     [DIVISIONNAME]  [varchar](320)    NULL,
     [LASTUSER]      [varchar](16)     NULL,
     [LASTUPDATE]    [datetime2](0)    NULL,
     [ME_LOCKID]     [varchar](100)    NULL,
     [ME_LOCKEXPIRE] [datetime2](0)    NULL,
     [ME_LOCKINFO]   [varchar](32)     NULL,
     [XGEN]          [varbinary](max)  NULL,
     CONSTRAINT [SYS_C0019108] PRIMARY KEY CLUSTERED ([XGENKEY] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
       ON [PRIMARY]
     ) 
     ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    </CreateStatement>
    <!-- BEGIN SEQUENCES -->
    
       <CreateStatement>
          CREATE INDEX EDS_DIVISION_IDX on EDS_Division( DIVISION ) <!-- syntax to be confirmed -->
       </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>
    

    Regards, Martin


    Martin Peter Clarke

    Friday, January 2, 2015 2:13 PM

Answers

  • Sorry not clear

    Are you trying to replicate the objects in SQLServer ?

    If yes what you can do is this

    declare @x xml='<?xml version="1.0" encoding="UTF-8"?>
    <SqlRepositoryConfig>
     <Tablename>EDS_Division</Tablename>
     <PrimaryKey>xgenkey</PrimaryKey>
     <XGenMsgDBField Type="BINARY" Format="standard">XGEN</XGenMsgDBField>
     <CreateStatement>
     CREATE TABLE [dbo].[EDS_DIVISION]
     (
     [XGENKEY]       [varchar](36)     NOT NULL,
     [DIVISION]      [varchar](32)     NULL,
     [DIVISIONNAME]  [varchar](320)    NULL,
     [LASTUSER]      [varchar](16)     NULL,
     [LASTUPDATE]    [datetime2](0)    NULL,
     [ME_LOCKID]     [varchar](100)    NULL,
     [ME_LOCKEXPIRE] [datetime2](0)    NULL,
     [ME_LOCKINFO]   [varchar](32)     NULL,
     [XGEN]          [varbinary](max)  NULL,
     CONSTRAINT [SYS_C0019108] PRIMARY KEY CLUSTERED ([XGENKEY] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
       ON [PRIMARY]
     ) 
     ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    </CreateStatement>
    <!-- BEGIN SEQUENCES -->
    
       <CreateStatement>
          CREATE INDEX EDS_DIVISION_IDX on EDS_Division( DIVISION ) <!-- syntax to be confirmed -->
       </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>'
    
    IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
    SELECT m.n.value('.[1]','varchar(max)') AS [Statement] INTO #temp
    FROM @x.nodes('/SqlRepositoryConfig/CreateStatement')m(n)
    
    DECLARE @Sql varchar(max)
    
    SELECT @Sql=STUFF((SELECT Statement + '; '
    FROM #temp
    FOR XML PATH('')
    ),1,1,'')
    
    EXEC (@Sql)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Friday, January 2, 2015 2:28 PM