Import XML File To Database (MS Server 2000) Using Procedure (transact Sql) ???

Jul 20, 2005

I must import some exemplary file to database (MS Srrver 2000) ofcourse
using procedure Transact SQL.
This file must:
1.Read the xml file
2. Create table
3. Import this date from xml file to my database

Ps. I create procedure who File xml imports to base, but unfortunately she
only schedule when earlier create a table or table is created.So I need (I
think) create such mini parser in language transact SQL.
Does someone have some ideas?

For every help Thanks

==== example file xml ===========================================
<root>
<Cust>
<IDosoby>1</IDosoby>
<Imie>Lukasz</Imie>
<Nazwisko>Przypadek</Nazwisko>
</Cust>
<Cust>
<IDosoby>2</IDosoby>
<Imie>Dariusz </Imie>
<Nazwisko>Mroz</Nazwisko>
</Cust>
<Cust>
<IDosoby>3</IDosoby>
<Imie>Tomasz</Imie>
<Nazwisko>Kolo</Nazwisko>
</Cust>
</root>
================================================== =========
--
Luk

View 3 Replies


ADVERTISEMENT

File Import To SQL Server 2000

Jul 20, 2005

Hi all,I have a file with an extension of .sdf. I "believe" it is a text fileof some sort but I am uncertain. The source agency hasn't returned anyof my calls so I'm wondering if anyone is familiar with this extension?I'd like to import the file into my database - when I use DTS and chosea text format, regardless of what delimiter I choose, the format isstill really ugly. when I pull it up in a huge text editor, it is hardfor me to tell what it is there.I saw in one of my searches that it could be a comma delimited (it'snot) .. could be a unisys file? I know it's not much information to goon - but where should I start in trying to get this into my databasewithout knowing the format? Any suggestions would be greatlyappreciated.Thanks!Bethany*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Import Complex XML File Into Sql Server 2000

Aug 21, 2007

I need help importing a complex xml file using the XML Bulk Load component. I need there to be 2 tables as shown below. I just
cannot seem to figure out how to get this to work with such a complex XML structure. I have shown below my table structure, a
sample of one of the entries of the XML files and what I have so far for my XSD schema. Any help would be great!!!
My Tables:CREATE TABLE [dbo].[WPXML] (    [Part] [varchar] (100) PRIMARY KEY,    [BaseVehicle] [int]  NULL ,    [Qty] [int]  NULL ,    [PartType] [int]  NULL ,    [EngineBase] [int]  NULL ,    [EngineDesignation] [int]  NULL ,    [ImageURL] [varchar] (100) NULL ,    [ThumbURL] [varchar] (100) NULL) GOCREATE TABLE [dbo].[WPPRODUCT] (    [Part] [varchar] (100) PRIMARY KEY ,    [PartNumber] [varchar] (100) NULL ,    [BrandID] [varchar] (4) NULL ,    [BrandDescription] [varchar] (100)  NULL ,    [Price] [varchar] (10) COLLATE  NULL ,    [ListPrice] [varchar] (10) COLLATE  NULL,    [Weight] [varchar] (10) COLLATE  NULL,    [Popularity] [varchar] (10)  NULL,    [OEFlag] [varchar] (10) NULL,    [ProductRemark] [varchar] (1000) NULL,    [Note] [varchar] (5000)  NULL ) GOSample of XML:<App action="A" id="1484266">   <BaseVehicle id= "5899"/>   <EngineBase id= "555"/>   <EngineDesignation id= "138"/>   <Qty>0</Qty>   <PartType id= "6192"/>   <Part>W0133-1621038</Part>   <Product>    <PartNumber>W0133-1621038</PartNumber>    <BrandID>FUL</BrandID>    <BrandDescription><![CDATA[Full]]></BrandDescription>    <Price>17.38</Price>    <ListPrice>36.60</ListPrice>    <Available>Y</Available>    <Weight>1.05</Weight>    <Popularity>B</Popularity>   </Product>   <Product>    <PartNumber>W0133-1611982</PartNumber>    <BrandID>KN</BrandID>    <BrandDescription><![CDATA[K&N Filters]]></BrandDescription>    <Price>68.78</Price>    <ListPrice>105.81</ListPrice>    <Available>Y</Available>    <Weight>1.80</Weight>    <Popularity>E</Popularity>   </Product>   <Product>    <PartNumber>W0133-1626304</PartNumber>    <BrandID>ND</BrandID>    <BrandDescription><![CDATA[Denso]]></BrandDescription>    <Price>22.34</Price>    <ListPrice>36.60</ListPrice>    <Available>Y</Available>    <OEFlag>OEM</OEFlag>    <Weight>1.05</Weight>    <notes>Notes For This Part</notes>    <Popularity>D</Popularity>   </Product>    <ImageURL><![CDATA[http://img.eautopartscatalog.com/live/W01331621038OES.JPG]]></ImageURL>   <ThumbURL><![CDATA[http://img.eautopartscatalog.com/live/thumb/W01331621038OES.JPG]]></ThumbURL>  </App>
My XSD Schema Thus Far:<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation>  <xsd:appinfo>    <sql:relationship name="test"        parent="WPXML"        parent-key="Part"        child="WPPRODUCT"        child-key="Part" />  </xsd:appinfo></xsd:annotation>  <xsd:element name="App" sql:relation="WPXML" sql:relationship="test">   <xsd:complexType>     <xsd:sequence>                 <xsd:element name="Qty" type="xsd:integer" />     <xsd:element name="Part" type="xsd:string" /> <xsd:element name="BaseVehicle">    <xsd:complexType>    <xsd:attribute name="BaseVehicle" type="xsd:integer" sql:field="BaseVehicle" />  </xsd:complexType>    </xsd:element>    <xsd:element name="PartType">    <xsd:complexType>    <xsd:attribute name="id" type="xsd:integer" sql:field="PartType" />   </xsd:complexType>    </xsd:element>    <xsd:element name="EngineBase">    <xsd:complexType>    <xsd:attribute name="id" type="xsd:integer" sql:field="EngineBase" />   </xsd:complexType>    </xsd:element>    <xsd:element name="EngineDesignation">    <xsd:complexType>    <xsd:attribute name="id" type="xsd:integer" sql:field="EngineDesignation" />   </xsd:complexType>    </xsd:element> <xsd:element name="ImageURL" type="xsd:string" /> <xsd:element name="ThumbURL" type="xsd:string" /> <xsd:element name="Product" sql:relation="WPPRODUCT" sql:key-fields="Part" sql:relationship="test">                  <xsd:complexType>             <xsd:sequence>              <xsd:element name="Part" type="xsd:string" />              <xsd:element name="PartNumber" type="xsd:string" >              </xsd:element>              <xsd:element name="BrandID" type="xsd:string" >              </xsd:element>  <xsd:element name="BrandDescription" type="xsd:string" >              </xsd:element>      <xsd:element name="Price" type="xsd:string" >             </xsd:element>             <xsd:element name="ListPrice" type="xsd:string" >             </xsd:element>             <xsd:element name="Weight" type="xsd:string" >             </xsd:element>             <xsd:element name="Popularity" type="xsd:string" >             </xsd:element>      <xsd:element name="OEFlag" type="xsd:string" >             </xsd:element>      <xsd:element name="ProductRemark" type="xsd:string" >             </xsd:element>      <xsd:element name="Note" type="xsd:string" >             </xsd:element>            </xsd:sequence>           </xsd:complexType>          </xsd:element>         </xsd:sequence>     </xsd:complexType>  </xsd:element></xsd:schema>
 

View 15 Replies View Related

DTS Import Excel File Using SQL Server 2000

Nov 2, 2007

I am trying to import an Excel file - when I pick the file I get the message "Could not open file for reading. Close any other application that may be locking the file."
I have verified that the file is not open - I have even rebooted the machine - still the same message - what am I doing different?
Please advise.
Thanks

View 2 Replies View Related

Data Import From CSV File With DTS (SQL Server 2000)

Oct 3, 2007

Hi All,
I am trying to import data via DTS from a CSV file. I have the "empty" tables already created with proper column names. Now, I have a subset data of each of these tables, which I am trying to import. I am facing problem in that, there is a datatype conflict between the source (CSV file) and destination (table already in the DB). All the data in the CSV file appears to be of DBTYPE_WSTR, where as in the table it is different (some are DBTYPE_WSTR, some are DBTYPE_DATE, and so on). Is there a way that I can import data successfully? This has become a work stoppage issue now. I had to actually go for this approach of creating the empty tables first and then importing data because, the backup file was very very large and could not be copied to our domain. Please help me out in this.

Thanks a lot.
Mannu.

View 4 Replies View Related

How To Import Data In Sql Server 2000 From An Excel(.xls) File

Feb 23, 2005

i want ot import data from excel .xls file to sql server 2000

into an existing table.
should i use some stored procedures or else

View 5 Replies View Related

How To Import An Oracle 8.1.7 Dump File Into SQL Server 2000 ?

Mar 2, 2005

Hi,
I do not know if it can be done:
how to import an Oracle dump file into SQL server ?

That dump file contains the content of a table, excported from Oracle.
Is there a way or another to import it to SQLServer 2000 ?

Thanks

View 2 Replies View Related

Import Human-Readable Text File Into SQL Server 2000

Jul 20, 2005

Hello,I am receiving a text file that is produced from a mainframe that isout of my control. I am attempting to find a (hopefully clean) way toimport it into a SQL Server database in an automated fashion. I amnot really concerned about how many tables it requires or what theschema looks like as long as the data remains related and ends up inits respective fields (I will probably use scratch tables for this).The data is given to me in a format that is meant to be printed outand read by human eyes (in a text file). The format looks somethinglike this:Begin File:-------------------------------------------------------------------------------1234 1234 1234 1234 XYZ Company 01/01/2003.......More stuff related to XYZ company for a couple of lines ..............(this stuff can easily be parsed by position).......MCARD VISA AMEX DISC-------------------------------------------------------------------------------TOTAL 11111.11 4444.44 5555.55 30.01TRANS FEE .20 .20 .15 .15TRANS AMOUNT 2222.22 888.89 833.33 4.50DISC .0165 .0165 .0365 .0355-------------------------------------------------------------------------------ANOTHER HEADER............More stuff related to XYZ Company................End File:Well, this isn't the exact format, but just an example. The point isthat all of the data in each column is related and should end up inthe same record which is related to the parent record of XYZ Company(or all in a single record in a single table if that is the closest Ican get).Also, the rows are not always present. For example, if TRANS FEEdoesn't apply to anything in the row, then the entire row willcollapse and TRANS AMOUNT would be the next line after TOTAL.I was looking at the bcp utility and dts, but dts doesn't seem to havethe performance capabilities (or reliability for that matter) I amlooking for. Bcp seems like it might work if there is some advancedformatting commands that I can't find in the documentation - Anyone?The best I can come up with is to use a high level language such as C#or VB.NET to parse the text file into another text file that is commadelimited, and then use the bcp utility (or bulk insert) to import itinto SQL Server where I can then use TSQL to manipulate it how I want.I am trying to eliminate the high level language parse and just gostraight from file to database. Does anybody know an easier route?TIA

View 1 Replies View Related

SQL Server 2000 Database Import

Mar 10, 2005

Help needed!

I am a final year uni student and as part of my project I've developed a .Net site with a SQL Server 2000 backend.

This week a virus hit our house and my PC got infected (despite anitvirus and firewall!). Anyway, I have had to format my hard disk and reinstall everything.

My site is back in the wwwroot folder and looks fine, trouble is I need to get the database back up and running.

Before I wiped the disk I took the .mdf and .ldf files from the data file in the SQL Server folder in Program Files.

How can I get my database backup and running.

Ive tried creating a new database (with the same name as the .mdf and .ldf files) and replacing the default created files with the copies I saved but this didnt work.

Any help would really be appreciated as I have to demo this to my lecturers soon and my whole grade is riding on it! (no pressure)

Matt

View 5 Replies View Related

Import Flat File To A Sql Server Database..

Feb 20, 2008

Hi.
   I want to upload a Flat file from an asp.net website to a Sql server.. I have used olebb provider to upload excel files and a foxpro provider to upload DBF files and then used the sqlbulkcopy to put the data to my sql server..
 
SO i was wondering what kinda provider should i use to import a Flat file to the database and do i have use a format file in order to import it...
 Any help will be appreciated..
Regards,
Karen

View 7 Replies View Related

SQL Server 2008 :: Import Of Excel Or Text File Into Database

Sep 11, 2015

I have an .xlsx file where I need to import the data into a table. If there is not a way to do this, is there a way to import either a tab del file or different type of .csv file into the database?

Do not want to use the SSIS or import feature from SQL2008 as I tried to save the steps and running it wont work either.

View 9 Replies View Related

Transact SQL :: How To Run Stored Procedure In Another Database On Different Server

Jul 13, 2015

I linked MS SQL and MySQL and i would like to have insert trigger on MySQL to import certain fields every time when there's new entry.

View 5 Replies View Related

How To Import A Text Database File Into SQL Server Management Studio Express

Aug 21, 2007

I have a text file with a bunch of zip codes:  99546,"AK","Adak",162.70,55.20,.0099551,"AK","Akiachak",161.39,60.89,.0099552,"AK","Akiak",161.19,60.89,.0099553,"AK","Akutan",165.78,54.14,.00................... I want to import this in Sql Server Express.  Is it possible to do this using Management Studio Express?  If not,  does anyone know a script that would import the data?

View 3 Replies View Related

SQL Server Admin 2014 :: Import Excel File On Database Failing

May 14, 2015

I have installed SQL 2014 (Evaluation Version) on testing machine. We want to import some excel files on database. I manually created one Test Database and now trying to import excel file. Import completed successfully but I am not able to see any table created as result of Import. I tried it 3-4 times and even restarted sql services but no luck.

View 3 Replies View Related

Import .dat File To SQL 2000

Oct 17, 2005

My company designs our own banking software written in Progress. Currently twice a year (during our releases) we require our bank customers to run a program on their system that pulls certain data and dumps it to a .dat file. This .dat file was then imported (using another progress program) into a Progress database that our development team created (called stats.db) so we could have access to certain information regarding the customer's software/hardware set-up and utilization of programs,etc. This stats.db is now going away as we have a centralized SQL database for all customer information. The new process will be for our customers to send in their .dat files, they would be forwarded onto me, then I will need to import them into our SQL database. I am struggling in finding a way to do this. Our .dat files contain one field that holds the information, therefore the data is not delimited in such a way that I can just pull it into Excel, Access, CSV, etc. How can I go about pulling data out of these .dat files into a view/table in SQL for importing?

I am at a total loss and have spent hours researching this issue. Any help will be greatly appreciated. Thanks...

View 3 Replies View Related

Import Data From MS Access Databases To SQL Server 2000 Using The DTS Import/Export

Oct 16, 2006

I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.

Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.

Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com

View 4 Replies View Related

SQL Server 2000 - Create An Excel File By Stored Procedure..

Mar 3, 2008

Can I create an excel file by stored procedure?

View 2 Replies View Related

How To Import An Mdf File Or Script Into Ms Sql 2000 Or 2005

Sep 16, 2007

 How can I import an mdf file or sql script in ms sql 2000 or
ms sql 2005, also how to do the same if it is am aceess database? Thank youDarlene 

View 1 Replies View Related

Transact SQL :: Import Bulk CSV Files In Database Table

Nov 12, 2015

I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.

View 21 Replies View Related

Sql Server 2000 To Mdf Database File

Apr 11, 2007

I know I know this is kind of reverse thing I am asking.but as I am working on project and it is very time consuming to connect every time I want to for modifying database or dataset. 

View 1 Replies View Related

Import/Export CSV File Via Stored Procedure

Jun 12, 2008

Reading through the forums, I found some great imformation for importing/exporting an excel spreadsheet via a stored procedure, however, the amount of data I have won't fit in excel. Can someone help me Import a CSV file via a stored procedure? and Export a CSV file via a stored procedure? I don't know if XML is the answer or if there is another way. For many reasons, I don't want to use DTS or Bulk. We are currently using SQL Server 2000.

Thank you,
Stacy



Thanks,
Stacy

View 1 Replies View Related

Stored Procedure - Import CSV File Into Table In Excel

Mar 3, 2014

I have the following code to import .csv file into my table in excel. It's being inserted into a table. dbo.ImportedPromoPricing. Table and the .csv file have 3 fields price, code and selling price.

Once import is completed I want to use the data in my dbo.ImportedPromoPricing to update another table dbo.MasterPricing. Records need to be compared and updated or appended if needed. in case of update only price will be updated. this is the beginning of my code

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[ImportPromoPricing] Script Date: 03/03/2014 14:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] .....

View 2 Replies View Related

How Do I Convert Data From CSV File To Database In SQL Server 2000 ?

Oct 24, 2006

Hi,How could i do to convert data from CSV files to tables on database in SQL server 2000 ? Please show me !Thank very muchdvl_lang 

View 3 Replies View Related

SQL Server 2000 Database Back Up File Size

Sep 27, 2004

I am having a bit of a mare on the database backup front - i.e. monster
DB, not a great deal of space to back it up into.

As I do the backup, the file on the disk seems to stay at 2KB

How does the file get written? Is there a temp somewhere first?


Cheers in advance - Dave

View 1 Replies View Related

Database Output Problem For A SQL Server 2000 Stored Procedure

Oct 27, 2006

Hi,  I have a problem with "database output"  window in executing a select-sp in vs 2005 standard. The db is a sql server 2000 one.When I execute the sp within VS, the database output correctly displays the execution information:No rows affected.(1 row(s) returned)@RETURN_VALUE = 3 but I can't see the returned rows (3 rows are returned); I only see the column names and no data.Running [dbo].[spBkm_GetList] ( @IDUser = <DEFAULT>, [.........]).IDBkm UIBkm IDUser --------------------- -------------------------------------- ---------
No rows affected. If I run the same sp in Sql Server Manager Studio Express it correctly shows the data of the 3 rows returned.  The sp uses  EXEC sp_executesql @Sql, @ParamList, ...... for executing the sql statement and the last line of sp is  RETURN @@ROWCOUNTI've tried removing the "RETURN @@ROWCOUNT" with no success.  The problem affects only one sp, the others, which are absolutely similar, work properly . I don't know what is the problem...Any idea?Thanks in advance 

View 1 Replies View Related

How To Import Sql 2000 Database Into Sql 2005

Mar 12, 2007

Hi,
I have developed an application in .NET 2005 using sql server 2005.
This is the enhancement to the existing application.Now whats the problem is, one of the customer wants his old database, which is in sql 2000 server with the new database(sql 2005 server). So, how can i import sqlserver 2000 database into sqlserver 2005 database.

Its very urgent plz.........
Regards,
Kishore

View 1 Replies View Related

Transact SQL :: Add Headers Name In CSV File From Stored Procedure

May 11, 2015

I want to add some name of the column's header from stored procedure to the CSV file

bcp "EXEC [databasname].[storedprocedure]" queryout C:estbcp_outputTable.csv -c -T -SPC01 -r


I'm using the code above in CMD.

Unfortunately, I cannot change the code that is inside of the stored procedure because it is already used in productionphase.

View 5 Replies View Related

Error Trying To Import MS Access 2003 Database Via SQL Server Import And Export Wizard - Too Many Sessions Already Active

Nov 29, 2006

I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.

I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.

Trouble is that it gets most of the way through the import until it spews forth the following error messages:

- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).

There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.

Does anyone know how I can get the import to work?

View 2 Replies View Related

To Import CSV File Into Database Table

Jun 3, 2008

Hai all.,
Im trying to import the CSV file values using bulk insert but im getting an error in my code .so can anyone help me on this.
the following is the coding i have created
--create table
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME
)
--import from CSV using bulk insert

BULK
INSERT CSVTest
FROM 'c:csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO

im gettin an error like
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "c:csvtest.txt" does not exist

The thing is i have created a CSV file in C drive with some values.

Thanks in advance

View 6 Replies View Related

Import Excel File To Database

Apr 11, 2007

Hai,



I am new to SSIS 2005.



Now i would like to use the foreach loop structure in an SSIS package to
loop through however many Excel files are placed in a directory and
then perform an import operation into a SQL table on each of these
files sequentially.



But i dont know how to get start?



Can anyone guide me on this task?



Thanks.

View 1 Replies View Related

How To Import The Database Data From Backup File ?

Nov 20, 2007

Hello,All:
             I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager.
             Now I want to import the  backup data(generated by production server),I don't know how to do it.
             Is it possible to  do it ? anyone can give me a soluation ?
             thanks in advanced!
    

View 1 Replies View Related

How To Import Data From Excel File To SDF Database

Jun 29, 2007

Hi Guys,



Can anyone tell me how can I achieve the file import of XLS file and read the data inside to insert into the respective table in my SDF database in the mobile devices?



Thanks.



Regards,

Jenson

View 8 Replies View Related

Transact SQL :: Why Does File Seem Displaced When Send Server Results To A File

Aug 24, 2015

I have the following SQL

IFOBJECT_ID('tempdb..#TempTable_Lockbox_File_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_File_Header_Record
;
IFOBJECT_ID('tempdb..#TempTable_Lockbox_Batch_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_Batch_Header_Record
;

[code]....

When I send my query results to a file in SQL Server Management Studio, how come I'm seeing the following in Notepad++? FH   TEST "FH" which I thought should be in a CHAR(2) data column is there but "TEST" seems to start in Column 6...not column 3 as I would have expected. I was expecting... FHTEST.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved