Importing xml through tsql

Hey
I'm relatively new to xml.
I want to upload xml data with sql's openxml. I can do it with "normal" xml
but my files look a bit different and I can't find help on it.
My file looks like this:
<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>
Hope this makes sense.
Thanks for the help.
Assuming you want the data in two different tables, you could write:
declare @.h int
exec sp_xml_preparedocument @.h output, N'<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>'
--insert into table1
select * from OpenXML(@.h, '/myfile/table1',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
--insert into table2
select * from OpenXML(@.h, '/myfile/table2',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
exec sp_xml_removedocument @.h
HTH
Michael
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:D66EB2F0-A4F4-4CAB-904C-B4550359905B@.microsoft.com...
> Hey
> I'm relatively new to xml.
> I want to upload xml data with sql's openxml. I can do it with "normal"
> xml
> but my files look a bit different and I can't find help on it.
> My file looks like this:
> <myfile>
> <table1>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table1>
> <table2>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table2>
> </myfile>
> Hope this makes sense.
> Thanks for the help.
>

Importing xml through tsql

Hey
I'm relatively new to xml.
I want to upload xml data with sql's openxml. I can do it with "normal" xml
but my files look a bit different and I can't find help on it.
My file looks like this:
<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>
Hope this makes sense.
Thanks for the help.Assuming you want the data in two different tables, you could write:
declare @.h int
exec sp_xml_preparedocument @.h output, N'<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>'
--insert into table1
select * from OpenXML(@.h, '/myfile/table1',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
--insert into table2
select * from OpenXML(@.h, '/myfile/table2',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
exec sp_xml_removedocument @.h
HTH
Michael
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:D66EB2F0-A4F4-4CAB-904C-B4550359905B@.microsoft.com...
> Hey
> I'm relatively new to xml.
> I want to upload xml data with sql's openxml. I can do it with "normal"
> xml
> but my files look a bit different and I can't find help on it.
> My file looks like this:
> <myfile>
> <table1>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table1>
> <table2>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table2>
> </myfile>
> Hope this makes sense.
> Thanks for the help.
>

importing XML Data into SQL Server 2000

Hi All,
I need to import data from a large XML file.
I do not want to use XML Bulk upload or use ADO components.
I am looking for a way to use sql server scripts to open
this file through a ODBC driver , OpenRowset function.
Are there any ODBC or OLEDB drivers exist that can be used
in OpenRowset function ?
Not that I know of.
You may want to look at using a stored proc and OpenXML (although XML
Bulkload is more efficient).
Sorry.
Michael
"vaga" <anonymous@.discussions.microsoft.com> wrote in message
news:00a101c49b3b$9b4c2150$a301280a@.phx.gbl...
> Hi All,
> I need to import data from a large XML file.
> I do not want to use XML Bulk upload or use ADO components.
> I am looking for a way to use sql server scripts to open
> this file through a ODBC driver , OpenRowset function.
> Are there any ODBC or OLEDB drivers exist that can be used
> in OpenRowset function ?
>

Importing text files to Sql Server using Asp.net/Vb.net

Hi,
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.

Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.Hi There,

I looked into using DTS packages to upload data from a spreadsheet about a year and a half ago, but decided against it due to the various problems associated. I opted for the calling of a stored procedure with a bulk insert statement to load the information from a CSV file. Here is c# code for the calling of a DTS package that was used, should be easy enough to convert to vb.net.
BTW you are using DTS; (add a reference to Interop.DTS, i think it is Microsoft DTSPackage Object Library)

All the best, John

string serverName = System.Configuration.ConfigurationSettings.AppSettings.Get("ServerName");
string serverPassword = System.Configuration.ConfigurationSettings.AppSettings.Get("ServerPassword");
string userName = System.Configuration.ConfigurationSettings.AppSettings.Get("UserName");

bool bSuccessful = true;
int pErrorCode;
int lHelpContext;
string sHelpFile;
string sInterfaceError;
string sErrSource;
string sErrDescription;

int DTSStepExecResult_Failure = 1;

Package2Class dtsPackage = new Package2Class();

object varPersistStgOfHost = null;

dtsPackage.LoadFromSQLServer(serverName, userName, serverPassword, DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null, null, null, DTSPackages.PackageName, ref varPersistStgOfHost);
dtsPackage.Execute();
// Check each step for failure
for (int stepCount=1; stepCount <= dtsPackage.Steps.Count; stepCount++)
{
if ((int)dtsPackage.Steps.Item(stepCount).ExecutionResult == DTSStepExecResult_Failure)
{

dtsPackage.Steps.Item(stepCount).GetExecutionErrorInfo(out pErrorCode, out sErrSource, out sErrDescription, out sHelpFile, out lHelpContext, out sInterfaceError);

bSuccessful = false;
}
}
dtsPackage.UnInitialize();|||hi John,

Thanks for the help, i do appreciate it! will try the code and see if i have any luck!
take care..

jen|||I do not see any part in the code that references the csv file.|||As the above code calls a DTS package that in turn loads the CSV file.

Importing Sqldatabase and Publishing it

HI,

I am working on a project in which a user can upload his database to the server...then he is able to view all the schema or execute any kind of query. Then he goes to some other machine and login and wants to download the database.

For example Alex have uploaded the database consisting of 1000 records to mysite. Then He goes to texas to find some oil wells. He is on another machine ... I download the database ... inseert new records and upload it back.

Its an example basically I want to know how to include the users' uploaded into my sqlserver and when required publish with data on his machine...

Thanks.

I suppose you're thinking about some disconnected scenario where you can upload a scheme, edit some rows, travel to a desert or mountain top, find oil, edit some rows offline and then synchronize the data with the server version. As far as I can see, you'll be needing a shared centralized SQL server and a personal/express edition on (let's say) a laptop. You can easily work offline and synchronize the two machines online either by replication or programmatically through a .NET application.

But since you're talking about a database with 1000 rows, you're thinking more of tables than databases. If this is true, you may consider exporting XML documents (with 1000 rows), edit the XML document (as if it were a real table) and upload the XML document later so the data can be synchronized. This is quite easy and commonly used. You can set up a database (many tables) where your customers can up- and download XML documents. You can use ASP.NET or SQL2005 to do the translation from XML to native data and back.

Greetings,

Robert

|||

Basically i am creating a web site where you will upload your database and then all the queries will be executed their.

Problem is your Databse should now be attached to my SQL Server. Attaching it programmatically is the basic problem right now. and then next when you want to download it to your local machine detaching it and publishing with data is my problem.

I tried my best to clear my problem...i think its clear now??

can you help on it.

Thanks

|||

Hi,

According to your question, I think that you should write a .sql file which can restore and backup the database and run it in the query analyzer. So you can download or upload the backup file and attach it to the SqlServer.

Hope it helps.

|||Thanks It worked