Re: how to insert xml into sql 2005
- From: "Mike C#" <xyz@xxxxxxx>
- Date: Sun, 20 Jan 2008 01:53:40 -0500
I see you gave up on your previous idea (or I assume you did since you
didn't bother answering the questions I asked). Anyways, here's a code
snippet if you want to avoid the MSXML/COM crap. Remember,
sp_xml_preparedocument automatically dedicates 1/8th of your server's memory
to XML processing no matter how big (or small) the XML document is.
DECLARE @xml XML;
SELECT @xml = BulkColumn
FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB)
Product
--Uncomment the next line to INSERT these values into a table
--INSERT INTO yourtable (id, description, url) -- ... --Add the remaining
columns here
SELECT x.value('@id', 'varchar(32)'),
x.value('Description', 'varchar(1024)'),
x.value('Url', 'varchar(1024)')
--... --Add the remaining columns here
FROM @xml.nodes('/Products/Product') x;
Unfortunately the XML data type isn't as complex an undertaking as all that
OPENXML stuff, and you don't have to remember to manually deallocate the
memory it uses, but you shouldn't have a problem adding the remaining
x.value(..., ...) method calls. Enjoy.
Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ACBE400E-4AD4-463C-AC5D-48DCC158C093@xxxxxxxxxxxxxxxx
Using the following code how do I actually insert the data from the xml
file
into the sql 2005 database:
declare @xml varchar(max)
declare @xmlHandle int
-- Bulk load the xml from the file
set @xml = (
select *
from openrowset
(
bulk 'C:\database\xmldtd\yahoostore.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'
,[description] varchar(1024) 'Description'
,url varchar(1024) 'Url'
,caption varchar(1024) 'Caption'
,captionnohtml varchar (1024) 'CaptionNoHTML'
,code varchar(32) 'Code'
,baseprice varchar (225) 'BasePrice'
,salesprice varchar (225) 'SalesPrice'
,categories varchar (225) 'Categories'
,thumb varchar (1024) 'Thumb'
,thumbheight varchar (255) 'ThumbHeight'
,thumbwidth varchar (255) 'ThumbWidth'
,picture varchar (1024) 'Picture'
,pictureheight varchar (255) 'PictureHeight'
,picturewidth varchar (255) 'PictureWidth'
,weight varchar (255) 'Weight'
,orderable varchar(3) 'Orderable'
,taxable varchar(3) 'Taxable'
,[path] varchar(1024) 'Path'
,localizedbaseprice varchar (255) 'LocalizedBasePrice'
,availability varchar (255) 'Availability'
,options varchar (3000) 'Options'
,sku varchar (255) 'SKU'
,optionid varchar (225) 'OptionId'
,optionname varchar (255) 'OptionName'
,optionparentid varchar (255) 'OptionParentID'
,productref_id varchar (255) 'ProductRef_ID'
,productref_url varchar (1024) 'ProductRef_URL'
,productref_name varchar (255) 'ProductRef_Name'
,itemsold varchar (255) 'ItemsSold'
,orders varchar (255) 'Orders'
,revenue varchar (255) 'Revenue'
,pageviews varchar (255) 'PageViews'
,item varchar (255) 'Item'
)
USE yahoostore
CREATE TABLE products
-- Never forget to remove the DOM document as soon as you're done using
it.
exec sp_xml_removedocument
@xmlHandle
go
Thanks
Dee
.
- Follow-Ups:
- Re: how to insert xml into sql 2005
- From: Dee
- Re: how to insert xml into sql 2005
- References:
- how to insert xml into sql 2005
- From: Dee
- how to insert xml into sql 2005
- Prev by Date: how to insert xml into sql 2005
- Next by Date: Re: Query from 2 XML data sources
- Previous by thread: how to insert xml into sql 2005
- Next by thread: Re: how to insert xml into sql 2005
- Index(es):
Relevant Pages
|
