Re: how to insert xml into sql 2005
- From: Dee <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 20 Jan 2008 10:43:01 -0800
Thanks Mike,
Will this work if the access data is set up mostly in querries?
Dee
"Mike C#" wrote:
See inline..
"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EDEDE245-EF55-4709-BA45-F943385D9961@xxxxxxxxxxxxxxxx
Thanks Mike,
For right now I just know I have to get this into the sql database. I
will
not be able to try this until Monday.
I also have two more questions. I am importing data from our store and
from
access what is the database we are getting off of.
Is there a way to run a comparison on the two databases? I will put them
into two different databases to compre with.
I assume you're asking if you can compare the contents of an Access database
to the contents of a SQL Server database. The short answer is yes, but with
some caveats. You can set up linked tables in Access that point at tables
in SQL Server and run comparisons in Access, or you may be able to do it in
reverse by setting up a linked server on SQL Server with an Access/Jet
database driver. The first option will probably be easier. There's no
guarantee that some SQL Server data types will be exactly the same as their
Access/Jet counterparts. The "approximate" data types like FLOAT and REAL
(in SQL) come to mind. So in some cases you may not be able to do exact
equality comparisons on some columns when comparing Access to SQL Server.
The second one is how can I automation the xml import ever evening. I
thought there was a way through maintenance. Is there true? If so where
or
how would I do this?
If you put the previous code in a stored procedure you can set up a SQL
Server Agent job to kick off the SP automatically on a regular schedule,
like hourly, daily, weekly, or whatever you need. Google "SQL Server Agent
Examples" for details, there are lots of tutorials on SQL Server Agent
available.
Thanks for your help.
Dee
NP
"Mike C#" wrote:
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: Mike C#
- Re: how to insert xml into sql 2005
- References:
- how to insert xml into sql 2005
- From: Dee
- Re: how to insert xml into sql 2005
- From: Mike C#
- Re: how to insert xml into sql 2005
- From: Dee
- Re: how to insert xml into sql 2005
- From: Mike C#
- how to insert xml into sql 2005
- Prev by Date: Re: how to insert xml into sql 2005
- Next by Date: Re: how to insert xml into sql 2005
- Previous by thread: Re: how to insert xml into sql 2005
- Next by thread: Re: how to insert xml into sql 2005
- Index(es):
Relevant Pages
|
