RE: how to import an xml file to sql

OK I got the openrowset to run, but it is not putting data into the database.
How do I do this.

Thanks
Dee

"Dee" wrote:

By reading in the xml with excel or flat files in pieces here is what I got
in sql scripts:

I could not import the data or not all of it doing it that way ran into
errors. The option field is very large so I made it more then 255. The
exactual xml file is 95,529 kb. Will it all get imported? Do I strill use
the info you sent me?

[Id] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Url] [nvarchar](255) NULL,
[Caption] [nvarchar](max) NULL,
[CaptionNoHTML] [nvarchar](max) NULL,
[Code] [nvarchar](255) NULL,
[BasePrice] [float] NULL,
[SalePrice] [float] NULL,
[Categories] [nvarchar](255) NULL,
[Thumb] [nvarchar](255) NULL,
[ThumbHeight] [float] NULL,
[ThumbWidth] [float] NULL,
[Picture] [nvarchar](255) NULL,
[PictureHeight] [float] NULL,
[PictureWidth] [float] NULL,
[Weight] [float] NULL,
[Orderable] [nvarchar](255) NULL,
[Taxable] [nvarchar](255) NULL,
[LocalizedBasePrice] [float] NULL,
[Availability] [nvarchar](255) NULL,
[options] [nvarchar](3000) NULL,
[SKU] [nvarchar](255) NULL,
[OptionID] [nvarchar](255) NULL,
[OptionName] [nvarchar](255) NULL,
[OptionParentID] [nvarchar](255) NULL
[Id] [nvarchar](255) NULL,
[ProductRef_ID] [nvarchar](255) NULL,
[ProductRef_Url] [nvarchar](255) NULL,
[ProductRef_Name] [nvarchar](255) NULL
[Items Sold] [float] NULL,
[Orders] [float] NULL,
[Revenue] [float] NULL,
[Page Views] [float] NULL,
[Item] [nvarchar](255) NULL,
[SKU] [nvarchar](255) NULL

Hope this was the information you wanted.

Thanks Dee


"Dee" wrote:

Thank you so much. I download booksonline and will view it.

For some reason, I could not get my SSIS open in the database, but I see
that the intergration manager is installed and running. Also for some
reason I could not get that create to work as it showed on the web site you
attached.

But I like you feel you have given me enough so I can start and see if it
works.

I will be testing all of this tomorrow. Reading tonight.

Thank you so much for the help and I will get the information needed.

Dee

"ML" wrote:

As you can see in my example, I guessed the data types. I was hoping to see
the table definition (CREATE TABLE statements), but I think you'll manage. :)

Adding columns is the easy part - just follow the example. Also, look up
OPENXML in Books Online - there are some nice examples there as well. In
fact, look up all keywords that are new to you in Books Online.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
.

Relevant Pages

  • RE: Excel 2003 Lists Vs Database
    ... Brevity is the soul of wit. ... "dee" wrote: ... Anything that we normally did with "tables" previously, including database ...
    (microsoft.public.excel.misc)
  • Re: SQLCMD - loop through sql scripts
    ... check current database version and will run appropriate sql scripts. ... you'll need to update your VersionInfo table afterward. ... REM Example usage: DatabaseUpdate.bat SERVER DB 9 ...
    (microsoft.public.sqlserver.server)
  • Re: Convert MySQL script to SQL Server script
    ... can load only the physical database, not SQL scripts. ... try to generate the script physically in MySQL database. ... 2.Then reverse engineer this database in CS2 again. ... the Help file of CS2> Contents ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL - Smoke and mirrors stuff
    ... trying sql scripts to achieve this. ... Add a reference to SQLDMO ... what I am trying to do is create a database (in initialization ... like 'Database in use by another process' and test fails. ...
    (microsoft.public.dotnet.languages.csharp)
  • SQL Script Distribution
    ... We have about 500 tables in our database. ... the programs and the SQL scripts. ... Does anyone have any experience with SQL script distribution? ... Prev by Date: ...
    (microsoft.public.vsnet.enterprise.tools)
Loading