insert xml into sql, xml has dtd

[Dee]

I have been trying to post this morning and it is not going through, so sorry
for deciding to start a new post.

I have use the code below and it creates the table and inserts one column,
but the columns seems empty. I do not seem to be able to get the information
that is in the xml file into sql.

Here is the code:
DECLARE @xml XML;
CREATE TABLE Products(xmlCol XML)
INSERT Products
SELECT CONVERT(XML, BulkColumn, 2)
FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB) AS
Products

Any suggestions? What do I need to do to insert the information from the
xml file into the sql database table along with the correct column names?

Thank you
Dee

[Mike C#]

Is it giving you an error message when you run this?

"Dee" <D...@discussions.microsoft.com> wrote in message
news:4A4D0548-CAA3-4A21...@microsoft.com...

[Mike C#]

BTW, SQL Server has very limited support for DTDs. Basically expanding
entities. It basically checks the DTD syntax to make sure it's correct,
expands entities, then strips the DTD away from the XML. If there is an
error in the DTD (not sure, since I haven't seen your XML data), the XML
data will not parse.

"Dee" <D...@discussions.microsoft.com> wrote in message
news:4A4D0548-CAA3-4A21...@microsoft.com...

[Dee]

Mike,

I found out that by removing the following line from my xml file maybe
things can get done.

DOCTYPE StoreExport SYSTEM "http://store.yahoo.com/doc/dtd/StoreExport.dtd">

Now I need to find out what code to use again because we have used so much.

This statement was huring my file, but it is in there any time I go and
retrive it.

Dee

[Mike C#]

Based on the snippet you posted, there are two things wrong with your file:

1) It's not well formed since the elements are not properly nested. This is
a basic requirement of XML. It looks like the items might have been edited
by hand, or with a bad HTML editor.

2) It's not valid against the DTD StoreExport.dtd. SQL Server doesn't check
external DTDs, so that's not an issue here. It will be an issue if you
expect another application to use your XML and it expects it to follow the
rules laid out in the StoreExport.dtd DTD.

You absolutely need to fix your XML if you plan to do any kind of processing
on it.

"Dee" <D...@discussions.microsoft.com> wrote in message

news:64E0DF49-CAA0-431F...@microsoft.com...