RE: how to import an xml file to sql

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: What tool to use for processing large documents
    ... cannot parse faster than the disk can read the XML data. ... Reading 10 GB off a disk will take around 3 to 5 minutes ... I forgot to mention that my logs are in zipped xml. ... Get the set of nodes matching an XPath expression. ...
    (comp.text.xml)
  • Re: More on BEGINFILE / ENDFILE
    ... What kind of errors show up while reading records that are catchable? ... Hummm.., you said that input errors are always fatal, and not reported throughout unredirected getline. ... instead go into the ENDFILE block with ERRNO set. ... In particular the XML extension of xgawk really needs a place to report errors in the middle of a file, without stopping further processing of other files. ...
    (comp.lang.awk)
  • Re: More on BEGINFILE / ENDFILE
    ... If reading via getline from a command-line file, ... reading input in XML mode is handled by feeding input text ... register int cnt; ... cnt = EOF; ...
    (comp.lang.awk)
  • Re: SISS vs Biztalk Large data
    ... I am too using SSIS instead of BizTalk to Process Large XML and Flat Files ...
    (microsoft.public.sqlserver.dts)
  • What is base URI?
    ... I am spending time reading articles on the Web, ... specs, Dave Winer's RSS spec, etc. for some of the other stuff I need ... am looking for a one-stop shop for all the XML related technologies. ...
    (comp.text.xml)