Two questions about moving data between a SQL DB and XML

[Jen]

1) I am exporting data from a SQL DB to an XML file. It works great but it
appears that null data in database records does not get put into the XML.
Is there a way to force it to always put the null data there instead of
omitting it?

2) How do I insert the data back into the SQL DB from the XML file?

 

[William \(Bill\) Vaughn]

Why are you transporting to XML? Why not use some other more compatible
format?


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Jen]

This is for a custom database export/import utility I'm writing. So I need
some kind of file format. Is there a better option than XML?

Basically the utility is exporting user-selected records from about a dozen
tables from one database and importing them into another database some time
later. The utility remaps primary key ids and writes the records into the
database. I have the key remapping done, that was easy. Just trying to
figure out how to write the DataSet into the database. I guess I'll just do
it the hard way: loop through records in each table and INSERT them. I was
just hoping there would be a quicker/easier way.

 

[Cor Ligthert [MVP]]

Jen,

If you are using the dataset.writeXML method, than just add the schema as a
second parameter, the null columns are not written, but they are used when
you want to import again.

Be aware that this is not such an efficient methode than as Bill told.

I use myself the dataset for another purpose, that did mean that I had to
use normal XML methods (XMLDocument) to make it with empty values.

Cor

 

[Jen]

Thanks for the reply.

I still don't understand what you mean by it's not efficient. What would a
better method of exporting data to a file format for later reimport be?
Efficiency (speed/size) really isn't that important to me. Easy of
implementation and human readability of the exported data is.

 

[Cor Ligthert [MVP]]

Jen,

I tell it for Bill, try the new book from Bill, he is giving mostly very
detailed information in this newsgroup too. But your message is a little bit
to vague on that point for that. Therefore, add it as question to Bill's
reply where you tell what the databases are that are involved.

He knows those methods better than me.

(I have made that method you have done yourself as well, however a little
bit senseless where I can use a backup and restore as well).

http://www.vb-tips.com/dbpages.aspx?Search=backup

Cor

 

[William \(Bill\) Vaughn]

If you're simply transporting data, use DTS/SSIS or the SqlBulkCopy class. I
would also consider transporting the data in a SQLCE (SQL Server Compact
Edition) database. It's light, fast and easy to implement.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------

 

[Jen]

Unfortunately my utility is doing more than transporting, it also has to
remap primary keys on import to avoid collisions.

 

[William \(Bill\) Vaughn]

What if you used a GUID (uniqueidentifier) as a PK? These don't have to be
remapped.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------