how to import an xml file to sql

I am using 2005 Enterprise and Standard. I need to know to to import an xml
file to both of these.
Thank you
Dee
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/
|||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/
|||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/
|||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/
|||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/
|||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.
idpathnameabstractcodepricesale-pricecostmap-pricemarginship-weightavailabilityorderabletaxableleafseat-size1arm-heightback-sizecapacitydepthweightexterior-sizeinterior-sizeheightlengthwidthoverall-dimensionsdegree-of-swivelmainbridgereturnsale-price-textseat-depthseat-heightseat-thicknessseat-widthreclinerottomanshipping-infoshipping-optionscolor-optionsdimsdiameterquantity-per-cartonright-columnout-of-stockcustom-orderquickship60-day-guaranteefree-shippingis-top-sellertemplate-numberback-height-from-seatcredenzahutchcustom-optionscontentsoptionalrelated-samplesUserIDcaptionflat-ship-rate1Inside-deliveryshipping-costManufacturermpnUPCISBNASIN-Amazon
Numbercollectionoptionsdeliveryshipping-pricekeywordsproduct-typemade-inage-rangePromoFree-shipping2departmentmaterialsgenderartist-designergreen-certifiedCA
Product SummaryCA Product Short NameCA Promo
Textproduct-urlVendorCompletedUpdated
101cpu-mcoMacoMaco Side Pount CPU Holder [101CPU-MCO]Maco Side Pount CPU
Holder
[101CPU-MCO]101CPU-MCO175.0089.9987.50$0.00FALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSE5"<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.00Maco Furniture101cpu$0.00steelschool
furniture, office furniture, home furnitureschool,
librarysteelFALSEhttp://www.bizchair.com/BizChair.comFALSE14-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/
|||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/
|||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/
|||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@.xmlvarchar(max)
declare@.xmlHandleint
-- Bulk load the xml from the file
set@.xml = (
select*
fromopenrowset
(
bulk'D:\Temp\Products.xml'
,single_blob
) Products
)
-- Prepare the DOM document
execsp_xml_preparedocument
@.xmlHandle output
,@.xml
-- Parse the XML
-- You can include this select statement in your insert statement
select*
fromopenxml
(
@.xmlHandle
,'/Products/Product'
)
with(
idvarchar(32)'@.Id'
,codevarchar(32)'Code'
,[description]varchar(1024)'Description'
,urlvarchar(1024)'Url'
,orderablevarchar(3)'NO'
,taxablevarchar(3)'NO'
,[path]varchar(1024)'Path'
,captionvarchar(1024)'Caption'
)
-- Never forget to remove the DOM document as soon as you're done using it.
execsp_xml_removedocument
@.xmlHandle
go
Let us know how you get along.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/