Home / server / Questions / 124031
In Process
Clutch
Asked: 2010-03-19 13:35:37 +0800 CST

Retrieve MS SQL database or table structure in XML

  • 772

Is there a way to export the database schema in well formed XML of a MS 2000 SQL Server. I'm looking for just the structure not the data and the more detailed the better. The XML may be used in a migration processes. I'm more familiar with MySQL then with SQL Server so please be detailed if you have time.

Thanks

sql-server xml

3 Answers

  1. How about using the information schema views or the underlying systems tables like sysobjects, syscolumns and then using the FOR XML clause to spit out some xml. Here's something to get you started:

     SELECT o.name as table_name
,c.name as column_name
,t.name as column_type
,t.length as column_length from sysobjects o 
    inner join syscolumns c on o.id=c.id
    inner join systypes t on c.xtype=t.xtype
    where o.xtype='u'
    FOR XML RAW
    • 2

  2. Just to show that it is possible, I can't tell you how to create code that will do it, but with some software my friend made, the schema .xml looks something like this. Maybe this can help jumpstart you to create your own code?

      <table enabled="true" name="Users">
    <field name="i_user_id" size="10" type="INTEGER"/>
    <field name="c_user_login" size="32" type="VARCHAR"/>
    <field name="c_user_pwd" size="32" type="VARCHAR"/>
    <field name="c_user_session" size="40" type="VARCHAR"/>
    <field name="c_user_name" size="200" type="VARCHAR"/>
    <field name="d_stamp_" size="19" type="DATETIME"/>
    <index name="Users2CID_0" unique="true" using="OTHER">
      <field direction="ASCENDING" name="i_user_id" ordinal="1"/>
    </index>
    <index name="Users2_1_idx" unique="false" using="OTHER">
      <field direction="ASCENDING" name="c_user_login" ordinal="1"/>
    </index>
    <index name="Users2_3_idx" unique="false" using="OTHER">
      <field direction="ASCENDING" name="c_user_session" ordinal="1"/>
    </index>
    <index name="Users2_4_idx" unique="false" using="OTHER">
      <field direction="ASCENDING" name="c_user_name" ordinal="1"/>
    </index>
    <index name="Users2_8_idx" unique="false" using="OTHER">
      <field direction="ASCENDING" name="c_flag_" ordinal="1"/>
    </index>
    <index name="Users2_9_idx" unique="false" using="OTHER">
      <field direction="ASCENDING" name="d_stamp_" ordinal="1"/>
    </index>
  </table>

    And then the data file looks like this:

    Users.dat

    i_user_id ( ,10,INTEGER,true,true,true) c_user_login ( ,32,VARCHAR,true,true,true)  c_user_pwd ( ,32,VARCHAR,true,true,true)    c_user_session ( ,40,VARCHAR,true,true,true)    c_user_name ( ,200,VARCHAR,true,true,true)  d_stamp_ ( ,19,DATETIME,true,true,true)
1   CUSER   Null    -1  CUser   2010-02-16 23:06:38.0
2   admin   X032343fZdYdgrtueuILPmQ==   -1  Admin User     2009-12-03 00:13:30.0
    • 0

  3. I wrote an application (dbscript) which generates an XML representation of a database schema (or single schema objects) after importing the schema from a database, or uploading an SQL DDL file.

    • 0