how to import an xml file to sql

Sorry for the delay.

You have to add an INSERT statement to the script.

E.g.:
Change this:
-- Parse the XML
-- You can include this select statement in your insert statement
select *

....to this:
-- Parse the XML
insert <table name>
(
<column list>
)
select <column list>
...


ML

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

Mike,

I ran the following and it worked:

DECLARE @x XML

SELECT @x =cast(bulkcolumn as XML)

FROM OPENROWSET(BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB) AS x
USE yahoostore
-- create a table variable
CREATE TABLE PRODUCTS (ProductID VARCHAR(255), ProductDesc VARCHAR(255), Url
VARCHAR(255), Caption VARCHAR(255),
CaptionNoHtml varchar (255), Code varchar(255), BasePrice varchar(255),
SalesPrice varchar(255), Categories varchar(255),
Thumb varchar(255), ThumbHeight varchar (255), ThumbWidth varchar (255),
Picture varchar(255), PictureHeight varchar (255),
Weight varchar(255), Orderable varchar(255), Taxable varchar(255), HTMLPath
varchar(255), LocalizedBasePrice varchar(255),
Availability varchar(255), Options varchar(255), SKU varchar(255), OptionID
varchar(255), OptionName varchar(255),
OptionParentID varchar (255), ProductRef_ID varchar(255), ProductRef_URL
varchar(255), ProductRef_Name varchar(255),
ItemsSold varchar(255), Orders varchar(255), Revenue varchar(255), PageViews
varchar(255), Item varchar(255))



INSERT INTO Products (ProductID, ProductDesc, Url, Caption, CaptionNoHtml,
Code, BasePrice, SalesPrice, Categories,
Thumb, ThumbHeight, ThumbWidth, Picture, PictureHeight, Weight, Orderable,
Taxable, HTMLPath, LocalizedBasePrice,
Availability, Options, SKU, OptionID, OptionName, OptionParentID,
ProductRef_ID, ProductRef_URL, ProductRef_Name,
ItemsSold, Orders, Revenue, PageViews, Item)

SELECT

x.value('@Id[1]','varchar(255)') AS id,
x.value('Description[1]','VARCHAR(255)') as description,
x.value('Url[1]','VARCHAR(255)') as url,
x.value('Caption[1]','VARCHAR(255)') as caption,
x.value('CaptionNoHtml[1]', 'VARCHAR(255)') AS captionnohtml,
x.value('Code[1]', 'VARCHAR(255)') AS code,
x.value('BasePrice[1]', 'VARCHAR(255)') AS baseprice,
x.value('SalesPrice[1]', 'VARCHAR(255)') AS salesprice,
x.value('Categories[1]', 'VARCHAR(255)') AS categories,
x.value('Thumb[1]', 'VARCHAR(255)') AS thumb,
x.value('ThumbHeight[1]', 'VARCHAR(255)') AS thumbheight,
x.value('ThumbWidth[1]', 'VARCHAR(255)') AS thumbwidth,
x.value('Picture[1]', 'VARCHAR(255)') AS picture,
x.value('PictureHeight[1]', 'VARCHAR(255)') as pictureheight,
x.value('Weight[1]', 'VARCHAR(255)') AS weight,
x.value('Orderable[1]', 'VARCHAR(255)') AS orderable,
x.value('Taxable[1]', 'VARCHAR(255)') AS taxable,
x.value('Path[1]', 'VARCHAR(255)') AS htmlpath,
x.value('LocalizedBasePrice[1]', 'VARCHAR(255)') AS localizedbaseprice,
x.value('Availability[1]', 'VARCHAR(255)') AS availability,
x.value('Options[1]', 'VARCHAR(255)') AS options,
x.value('SKU[1]', 'VARCHAR(255)') AS sku,
x.value('OptionID[1]', 'VARCHAR(255)') AS optionid,
x.value('OptionName[1]', 'VARCHAR(255)') AS optionname,
x.value('OptionParentID[1]', 'VARCHAR(255)') AS optionparentid,
x.value('ProductRef_ID[1]', 'VARCHAR(255)') AS productrefid,
x.value('ProductRef_URL[1]', 'VARCHAR(255)') AS productrefurl,
x.value('ProductRef_Name[1]', 'VARCHAR(255)') AS productrefname,
x.value('ItemsSold[1]', 'VARCHAR(255)') AS itemssold,
x.value('Orders[1]', 'VARCHAR(255)') AS orders,
x.value('Revenue[1]', 'VARCHAR(255)') AS revenue,
x.value('PageViews[1]', 'VARCHAR(255)') AS pageviews,
x.value('Item[1]', 'VARCHAR(255)') AS item


FROM @x.nodes('/StoreExport/Products/Product') s(x)


I have another question, but it is regarding xsd. Should I start another
post for this.

Thank you
Dee

"ML" wrote:

> Sorry for the delay.
>
> You have to add an INSERT statement to the script.
>
> E.g.:
> Change this:
> -- Parse the XML
> -- You can include this select statement in your insert statement
> select *
>
> ...to this:
> -- Parse the XML
> insert <table name>
> (
> <column list>
> )
> select <column list>
> ...
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql 

If it's another issue then consider starting a new thread, note that this
particualr newsgroup is dedicated to data warehousing.

If the question has to do with T-SQL, you'll have more luck in this newsgroup:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public....server.

If the question has to do with XML (and XML Schema) in SQL Server:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public....server.

If it's a general XML Schema question you could also try here:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.xml


ML

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

Mike,

I would like to thank you so much. You help and gave me ideas where to
research.

Thank you so much for you time and patience with someone struggling.

Dee

"ML" wrote:

> If it's another issue then consider starting a new thread, note that this
> particualr newsgroup is dedicated to data warehousing.
>
> If the question has to do with T-SQL, you'll have more luck in this newsgroup:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public....server.
>
> If the question has to do with XML (and XML Schema) in SQL Server:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public....server.
>
> If it's a general XML Schema question you could also try here:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.xml
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: how to import an xml file to sql