Is it a good to replace SQL script files with XML files?

[serge]

I am thinking about replacing the INSERT data script
files that I have with XML files. This way I can open the XML
file using an XML Editor and see the values in a GRID and
make changes easier.

Do you see any problem with this approach?

I managed to put together some code that is exporting
a SQL table with its data to an XML file and also a code
that reads the XML file's data and inserts it into a table.

Now I am researching on XSD, td:datatype, DTD...
(I am new to XML) in order to figure out how I can
use a single xml file that will hold both the sql server
fields, the datatypes and their values.

If you have links to some sample code that has anything
to do with the datatype export and import I am working
on, can you please share them with me?

Most importantly what do you think about the idea of using
XML files vs sql scripts?
Thank you

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

I am thinking about replacing the INSERT data script
files that I have with XML files. This way I can open the XML
file using an XML Editor and see the values in a GRID and
make changes easier.

Do you see any problem with this approach?

I know too little XML to say that whether this is good or bad. I didn't
know that there were XML Editors where you could edit grid cells.

I recognize the problem, though, because we have plenty of such files in
our shop. Our solution to the problem is Excel. (Which can be saved as
XML, but we don't do that currently.) Then we have a tool that reads the
Excel book and generates an INSERT-file from it. That file, by the way, does
not include any INSERT statements, but calls to a stored procedure that
will insert or update (or delete), so that the files easily can be rerun.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[serge]

I have only started learning XML 3-4 days ago. I ran into a
newsgroup post by chance where someone was using XML
to transfer data to SQL Server.

http://visualbasic.ittoolbox.com/gro...ver-l&i=780204

So that made me wonder why I wouldn't do that?
I've been working on this since then and slowly learning more
about SELECT * FROM TABLE FOR XML AUTO, XML,
DTD, XSD files, now I need to learn XDR, I think XDR is
similar to XSD but seems to be aimed for SQL Server.
I'll post some questions on microsoft.public.xml and hopefully
I'll get some answers from people who have already done what
I am trying to do.

But one question I have is if you are using Excel, are you using
it only for the INSERT data part? What about using the same
or another Excel file to hold the table's column names and data
types?

At this point in time (with my very little knowledge of XML) I
believe it wouldn't be a good idea to replace the sql files holding
the table structures with XML files holding the equivalent in terms
of the columns and its data types. I think that is more difficult
for someone to make table changes.

Here are three links for free XML Editors.
http://www.xmlcooktop.com/

I like these two as they will show you the data in grids:

http://symbolclick.com/index.htm
http://www.xmlfox.com/download.htm
Thanks

I know too little XML to say that whether this is good or bad. I didn't
know that there were XML Editors where you could edit grid cells.

I recognize the problem, though, because we have plenty of such files in
our shop. Our solution to the problem is Excel. (Which can be saved as
XML, but we don't do that currently.) Then we have a tool that reads the
Excel book and generates an INSERT-file from it. That file, by the way,
does
not include any INSERT statements, but calls to a stored procedure that
will insert or update (or delete), so that the files easily can be rerun.

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

But one question I have is if you are using Excel, are you using
it only for the INSERT data part? What about using the same
or another Excel file to hold the table's column names and data
types?

I might be misunderstanding your questions, but for that purpose a
data-modelling tool is much better in my opinion. In our shop we
use PowerDesigner from Sybase.

(Incidently, you can save the data model in XML format. But the main
point with that is if you keep the model under verison control, you
can use a standard diff tool to see the differences between two versions.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[serge]

>I might be misunderstanding your questions, but for that purpose a

data-modelling tool is much better in my opinion. In our shop we
use PowerDesigner from Sybase.

For some reason my Outlook Express is not downloading your last
post.

I checked the demo of PowerDesigner from Sybase. Modeling tool
is something I will have to look into in the next weeks/months.

Thanks