Create an XML Schema File from a SQL Server Database

 Keywords: SQL Server Database XML Schema System.Data System.Data.OleDb.OleDbConnection System.Data.DataTable SQLOLEDB.1 .Net dotNet

Attached is a script that uses WB's CLR to create an xml schema file from a SQL Server Database. The oledb classes in the .NET Framework are not a huge leap from COM ADO, and results are delivered very fast.

;Winbatch 2013B - Basics with CLR and SQL Server (tested using 2008 Express R2 )
;
;ADO functionality is built into the .NET Framework, This uses Version 4, but should work
;with earlier versions.
;
;The script creates a local xml file based on a schema query into the Master database
;(you can substitute your own database to test, but Master is in all SQL Servers).
;Windows Authentication used in connection string.
;
;Stan Littlefield, January 8, 2014
;////////////////////////////////////////////////////////////////////////////////////////////////////

ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
cXML=dirscript():"master.xml"
If FileExist(cXML) Then FileDelete(cXML)
svr = ".\SQLEXPRESS"
db = "Master"
cConn ="Provider=SQLOLEDB.1;Data Source=":svr:";Database=":db:";Integrated Security=SSPI;Persist Security Info=False"
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
oProvider.Open()
oTables = ObjectClrNew("System.Data.DataTable")
oTables = oProvider.GetSchema("Tables")
oTables.WriteXml(cXML) 

oTables = 0
oProvider.Dispose()
oProvider=0

If FileExist(cXML) Then Display(2,"Schema File Created for: ":db,cXML)
Exit
;////////////////////////////////////////////////////////////////////////////////////////////////////////////

Article ID:   W17810

Filename:   Create an XML Schema File from a SQL Server Database.txt

File Created: 2014:01:08:09:52:40

Last Updated: 2014:01:08:09:52:40

Database Search

TechHome

Create an XML Schema File from a SQL Server Database