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'
...

I executed the program this morning and got msg102. I looked it up, but do
not see what it is referring to in this code:
DECLARE @xml XML;
SELECT @xml = BulkColumn
FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB)
Product

SELECT x.value('@id', 'varchar(32)'),
x.value('Description', 'varchar(1024)'),
x.value('Url', 'varchar(1024)'),
x.value('Caption', 'varchar(1024'),
x.value('CationNoHtml', 'varchar(1024)'),
x.value('Code', 'varchar(1024)'),
x.value('BasePrice', 'varchar(1024)'),
x.value('SalesPrice', 'varchar(1024)'),
x.value('Categories', 'varchar(1024'),

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ')'.

Again thank you for you help.
Dee

x.value('Categories', 'varchar(1024'),

Change this line to the following:

x.value('Categories', 'varchar(1024)'),

"Dee" discussions.microsoft.com> wrote in message
news:E9BC5B19-60F1-4512-9B96-5653E3EC3C43@microsoft.com...

Hello Mike,

I also got an error in:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'FROM'.

"Mike C#" wrote:

Here is the code and I can not find an error any more:

DECLARE @xml XML;
SELECT @xml = BulkColumn
FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB)
Product

SELECT x.value('@id', 'varchar(32)'),
x.value('Description', 'varchar(1024)'),
x.value('Url', 'varchar(1024)'),
x.value('Caption', 'varchar(1024)'),
x.value('CationNoHtml', 'varchar(1024)'),
...

Here is the code and I can not find an error:

DECLARE @xml XML;
SELECT @xml = BulkColumn
FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB)
Product

SELECT x.value('@id', 'varchar(32)'),
x.value('Description', 'varchar(1024)'),
x.value('Url', 'varchar(1024)'),
x.value('Caption', 'varchar(1024)'),
x.value('CationNoHtml', 'varchar(1024)'),
...

Just one change:

x.value('Item', 'varchar(1024)'), -- <--- get rid of trailing comma in the
next to last line

"Dee" discussions.microsoft.com> wrote in message
news:821E06AB-18B9-4A09-8BE5-236C815E4C6A@microsoft.com...

Found errors on my part and fixed them missing ( or ). Still get an error at
FROM.

Dee

"Dee" wrote:

You have an extra comma at the end of the line before the FROM clause.

"Dee" discussions.microsoft.com> wrote in message
news:94A6DEB3-8BDC-46A5-8891-70E2BB336F5E@microsoft.com...

Sorry to keep posting, but I do look these up first, just not getting the
correct error of what I need to change.

I am now getting:

Msg 318, Level 15, State 0, Line 42
The table (and its columns) returned by a table-valued method need to be
aliased.

Also will this insert the data from the xml file into the database?

Thank you and I am trying to find these things out as I go.
Dee

"Mike C#" wrote: