RE: 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/
.

Loading