how to import an xml file to sql

This is store data and is very large. I was hoping not to write all that
code and do not know how to execute all the applications for Bulk Load. The
xml file may need to be cleaned up as well.

Any further help will be appreciated. I do not know how to use openrowset.

Thank you
Dee

"ML" wrote:

> Look up OPENROWSET in Books Online, specifically - look at the BULK option.
> http://msdn2.microsoft.com/en-us/library/ms190312.aspx
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

Have you tried the examples in Books Online?

You could also use SSIS to parse the XML and extract the information you
need, either way - you won't be able to avoid at least a bit of coding.

If you post a sample of the XML (not the whole thing, of course) then we can
help you come up with a more accurate solution.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

Here is a small example:

<Product Id="-av1011-bass">
<Code>AV1011-FS-BASS</Code>
<Description>Bass Industries Avalon [AV1011-FS-BASS]</Description>
<Url>http://www.bizchair.com/-av1011-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption><li>A blend of contemporary edge, softness and
comfort</li><li> A great synergy of design, special relationship and
function</li><li>Life time warranty on frames, springs, and moving
parts</li><li>2 year warranty</li><li><b>Come to BizChair.com for all your
Home Theater Furniture and for all your Home Furniture needs!</b></Caption>
</Product>
<Product Id="-ma1061-bass">
<Code>MA1061-FS-BASS</Code>
<Description>Bass Industries Matinee Lounger
[MA1061-FS-BASS]</Description>
<Url>http://www.bizchair.com/-ma1061-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption><li>Available in Black Leather Only</li><li>Straight Row
Only</li><li>Motorized Recline</li><li>Chaise Styling</li><li>Space
Saver</li><li>Life time warranty on all frames, springs and moving
parts</li><li>5 year warranty on leather</li><li>2 year warranty on all other
materials</li><li><b>Come to BizChair.com for all your Home Theater Furniture
and for all your Home Furniture needs!</b></Caption>
</Product>

For books online do I just look for SSIS?

Thanks
Dee

"ML" wrote:

> Have you tried the examples in Books Online?
>
> You could also use SSIS to parse the XML and extract the information you
> need, either way - you won't be able to avoid at least a bit of coding.
>
> If you post a sample of the XML (not the whole thing, of course) then we can
> help you come up with a more accurate solution.
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

That's nice! Thanks! Now if you'd just post an example of the output you need.

Regarding SSIS - you could start with Books Online. There's also a web site
dedicated to SSIS:
http://www.sqlis.com/


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

We were creating our store in access and we used queries. Our store was
housed on yahoo. We are getting away from the yahoo store and going to
another hosting company that supports SQL.

Here is a line from hone of our queries in access and I that they would like
for it to look the same in SQL but now in queries.

id path name abstract code price sale-price cost map-price margin ship-weight availability orderable taxable leaf seat-size1 arm-height back-size capacity depth weight exterior-size interior-size height length width overall-dimensions degree-of-swivel main bridge return sale-price-text seat-depth seat-height seat-thickness seat-width recliner ottoman shipping-info shipping-options color-options dims diameter quantity-per-carton right-column out-of-stock custom-order quickship 60-day-guarantee free-shipping is-top-seller template-number back-height-from-seat credenza hutch custom-options contents optional related-samples UserID caption flat-ship-rate1 Inside-delivery shipping-cost Manufacturer mpn UPC ISBN ASIN-Amazon
Number collection options delivery shipping-price keywords product-type made-in age-range Promo Free-shipping2 department materials gender artist-designer green-certified CA
Product Summary CA Product Short Name CA Promo
Text product-url Vendor Completed Updated
101cpu-mco Maco Maco Side Pount CPU Holder [101CPU-MCO] Maco Side Pount CPU
Holder
[101CPU-MCO] 101CPU-MCO 175.00 89.99 87.50 $0.00 FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 5 "<li>Under
desk side mount design is
expandable in depth only</li><li>Will fit all
brands of furniture</li><li>Keeps your CPU
elevated from dusty floors</li><li>Side
mount onl</li><li>16 gauge steel<\li><li>Perfect for school or library
settings</li><li>20" $0.00 Maco Furniture 101cpu $0.00 steel school
furniture, office furniture, home furniture school,
library steel FALSE http://www.bizchair.com/ BizChair.com FALSE 14-Jan-08


Thanks Dee

"ML" wrote:

> That's nice! Thanks! Now if you'd just post an example of the output you need.
>
> Regarding SSIS - you could start with Books Online. There's also a web site
> dedicated to SSIS:
> http://www.sqlis.com/
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

Could you also post the table DDL as the text in your previous post didn't
quite make it accross as you'd expected.

For info on how to do that see this article:
http://www.aspfaq.com/etiquette.asp?id=5006


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

I can't seem to get mine to work.

Thanks
Dee

"ML" wrote:

> Could you also post the table DDL as the text in your previous post didn't
> quite make it accross as you'd expected.
>
> For info on how to do that see this article:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

What doesn't seem to work?

Ok. I fixed your XML (it was not well-formed):
<?xml version="1.0" encoding="UTF-8"?>
<Products>
<Product Id="-av1011-bass">
<Code>AV1011-FS-BASS</Code>
<Description>Bass Industries Avalon [AV1011-FS-BASS]</Description>
<Url>http://www.bizchair.com/-av1011-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption>
<li>A blend of contemporary edge, softness and
comfort</li>
<li>A great synergy of design, special relationship and
function</li>
<li>Life time warranty on frames, springs, and moving
parts</li>
<li>2 year warranty</li>
<li>
<b>Come to BizChair.com for all your
Home Theater Furniture and for all your Home Furniture needs!</b>
</li>
</Caption>
</Product>
<Product Id="-ma1061-bass">
<Code>MA1061-FS-BASS</Code>
<Description>Bass Industries Matinee Lounger
[MA1061-FS-BASS]</Description>
<Url>http://www.bizchair.com/-ma1061-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption>
<li>Available in Black Leather Only</li>
<li>Straight Row
Only</li>
<li>Motorized Recline</li>
<li>Chaise Styling</li>
<li>Space
Saver</li>
<li>Life time warranty on all frames, springs and moving
parts</li>
<li>5 year warranty on leather</li>
<li>2 year warranty on all other
materials</li>
<li>
<b>Come to BizChair.com for all your Home Theater Furniture
and for all your Home Furniture needs!</b>
</li>
</Caption>
</Product>
</Products>

Save it to a folder (in my case that's "D:\Temp\Products.xml" - referenced
in the OPENROWSET function).

Then execute this example:

declare @xml varchar(max)
declare @xmlHandle int

-- Bulk load the xml from the file
set @xml = (
select *
from openrowset
(
bulk 'D:\Temp\Products.xml'
,single_blob
) Products
)

-- Prepare the DOM document
exec sp_xml_preparedocument
@xmlHandle output
,@xml

-- Parse the XML
-- You can include this select statement in your insert statement
select *
from openxml
(
@xmlHandle
,'/Products/Product'
)
with (
id varchar(32) '@Id'
,code varchar(32) 'Code'
,[description] varchar(1024) 'Description'
,url varchar(1024) 'Url'
,orderable varchar(3) 'NO'
,taxable varchar(3) 'NO'
,[path] varchar(1024) 'Path'
,caption varchar(1024) 'Caption'
)

-- Never forget to remove the DOM document as soon as you're done using it.
exec sp_xml_removedocument
@xmlHandle
go

Let us know how you get along.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

Ooopsss!

Here's the corrected query:

declare @xml varchar(max)
declare @xmlHandle int

-- Bulk load the xml from the file
set @xml = (
select *
from openrowset
(
bulk 'D:\Temp\Products.xml'
,single_blob
) Products
)

-- Prepare the DOM document
exec sp_xml_preparedocument
@xmlHandle output
,@xml

-- Parse the XML
-- You can include this select statement in your insert statement
select *
from openxml
(
@xmlHandle
,'/Products/Product'
)
with (
id varchar(32) '@Id'
,code varchar(32) 'Code'
,[description] varchar(1024) 'Description'
,url varchar(1024) 'Url'
,orderable varchar(3) 'Orderable'
,taxable varchar(3) 'Taxable'
,[path] varchar(1024) 'Path'
,caption varchar(1024) 'Caption'
)

-- Never forget to remove the DOM document as soon as you're done using it.
exec sp_xml_removedocument
@xmlHandle
go


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

OK, I will use this. Is this the output you want me to send back to your.
Also I think there are other fields I will have to add.

Thanks
Dee

"ML" wrote:

> Ooopsss!
>
> Here's the corrected query:
>
> declare @xml varchar(max)
> declare @xmlHandle int
>
> -- Bulk load the xml from the file
> set @xml = (
> select *
> from openrowset
> (
> bulk 'D:\Temp\Products.xml'
> ,single_blob
> ) Products
> )
>
> -- Prepare the DOM document
> exec sp_xml_preparedocument
> @xmlHandle output
> ,@xml
>
> -- Parse the XML
> -- You can include this select statement in your insert statement
> select *
> from openxml
> (
> @xmlHandle
> ,'/Products/Product'
> )
> with (
> id varchar(32) '@Id'
> ,code varchar(32) 'Code'
> ,[description] varchar(1024) 'Description'
> ,url varchar(1024) 'Url'
> ,orderable varchar(3) 'Orderable'
> ,taxable varchar(3) 'Taxable'
> ,[path] varchar(1024) 'Path'
> ,caption varchar(1024) 'Caption'
> )
>
> -- Never forget to remove the DOM document as soon as you're done using it.
> exec sp_xml_removedocument
> @xmlHandle
> go
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

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/
 >> Stay informed about: how to import an xml file to sql 

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/
 >> Stay informed about: 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/
 >> Stay informed about: how to import an xml file to sql