> Do me a favor, try opening the XML file in Internet Explorer. What result
> do you get?
>
> "Dee" discussions.microsoft.com> wrote in message
> news:73260B01-419E-4E93-9D56-488BEEE88ADB@microsoft.com...

>>I have tried the following and only get one column in the database and it
>>can
>> not be read. It seems as if nothing is in it and it takes a long time to
>> open.
>>
>> DECLARE @xml XML;
>> use yahoostore
>> CREATE TABLE Products(xmlCol XML)
>> INSERT Products
>>
>> SELECT
>> -- @xml = BulkColumn
>> CONVERT(XML, BulkColumn, 2)
>> FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml', SINGLE_BLOB)xyz
>>
>> Do you have any suggestions?
>>
>> Thank you
>> Dee
>>
>>
>>
>> "Dee" wrote:
>>

>>> Mike this is what I am getting in the table:
>>> USE [yahoostore]
>>> GO
>>> /****** Object: Table [dbo].[Products] Script Date: 01/21/2008
>>> 13:54:11
>>> ******/
>>> SET ANSI_NULLS ON
>>> GO
>>> SET QUOTED_IDENTIFIER ON
>>> GO
>>> CREATE TABLE [dbo].[Products](
>>> [xmlCol] [xml] NULL
>>> ) ON [PRIMARY]
>>>
>>> Thanks
>>> Dee
>>>
>>>
>>> "Dee" wrote:
>>>

>>>> I never did get
>>>>
>>>> FROM @xml.nodes('/Products/Product') Products (x);
>>>>
>>>> to work. I am reading and trying. What is the xyz?
>>>>
>>>> So far I am getting nothing to work and errors message and I try to
>>>> find out
>>>> what they are and try what ever I can.
>>>>
>>>> Thanks
>>>> Dee
>>>>
>>>>
>>>>
>>>> "Mike C#" wrote:
>>>>

>>>>> I assume, since you moved on to the next step of adding the INSERT,
>>>>> that you
>>>>> got the shred working properly with no errors. By "directory" I
>>>>> assume you
>>>>> mean "table". SQL Server provides only very limited support for DTDs.
>>>>> Try
>>>>> giving OPENXML a correlation name:
>>>>>
>>>>> SELECT CONVERT(XML, BulkColumn, 2)
>>>>> FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml',
>>>>> SINGLE_BLOB) xyz
>>>>>
>>>>>
>>>>>
>>>>> "Dee" discussions.microsoft.com> wrote in message
>>>>> news:A87085E2-821A-4CC7-B17A-8FBBAD707C26@microsoft.com...

>>>>>> Mike,
>>>>>>
>>>>>> I got the following message with that:
>>>>>>
>>>>>> Msg 2389, Level 16, State 1, Line 8
>>>>>> XQuery [value()]: 'value()' requires a singleton (or empty
>>>>>> sequence),
>>>>>> found
>>>>>> operand of type 'xdt:untypedAtomic *'
>>>>>>
>>>>>>
>>>>>> I also tried using this because my xml file has dtd in it, hence
>>>>>> the
>>>>>> directory xmldtd:
>>>>>>
>>>>>> DECLARE @xml XML;
>>>>>> CREATE TABLE Products(xmlCol XML)
>>>>>> INSERT Products
>>>>>>
>>>>>> SELECT
>>>>>> -- @xml = BulkColumn
>>>>>> CONVERT(XML, BulkColumn, 2)
>>>>>> FROM OPENROWSET (BULK 'C:\database\xmldtd\yahoostore.xml',
>>>>>> SINGLE_BLOB)
>>>>>>
>>>>>> Product
>>>>>>
>>>>>> SELECT * FROM Product
>>>>>>
>>>>>>
>>>>>> It create a directory named produdcts, but the only column was an
>>>>>> xml
>>>>>> column
>>>>>> with nothing in it.
>>>>>>
>>>>>> Dee
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> "Dee" wrote:
>>>>>>

>>>>>>> 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:
>>>>>>>

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

>>>>>>>>> Found errors on my part and fixed them missing ( or ). Still
>>>>>>>>> get an
>>>>>>>>> error
>>>>>>>>> at
>>>>>>>>> FROM.
>>>>>>>>>
>>>>>>>>> Dee
>>>>>>>>>
>>>>>>>>> "Dee" wrote:
>>>>>>>>>

>>>>>>>>>> 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)'),
>>>>>>>>>> x.value('Code', 'varchar(1024)'),
>>>>>>>>>> x.value('BasePrice', 'varchar(1024)'),
>>>>>>>>>> x.value('SalesPrice', 'varchar(1024)'),
>>>>>>>>>> x.value('Categories', 'varchar(1024)'),
>>>>>>>>>> x.value('Thumb', 'varchar(1024)'),
>>>>>>>>>> x.value('ThumbHeight', 'varchar(1024)'),
>>>>>>>>>> x.value 'ThumbWidth', 'varchar(1024)'),
>>>>>>>>>> x.value('Picture', 'varchar(1024)'),
>>>>>>>>>> x.value('PictureHeight', 'varchar(1024)'),
>>>>>>>>>> x.value('Weight', 'varchar(1024)'),
>>>>>>>>>> x.value('Orderable', 'varchar(1024)'),
>>>>>>>>>> x.value('Taxable', 'varchar(1024)'),
>>>>>>>>>> x.value('Path', 'varchar(1024)'),
>>>>>>>>>> x.value('LocalizedBasePrice', 'varchar(1024)'),
>>>>>>>>>> x.value('Availability', 'varchar(1024)'),
>>>>>>>>>> x.value('Options', 'varchar(1024)'),
>>>>>>>>>> x.value('SKU', 'varchar(1024)'),
>>>>>>>>>> x.value 'OptionID', 'varchar(1024)'),
>>>>>>>>>> x.value('OptionName', 'varchar(1024)'),
>>>>>>>>>> x.value('OptionParentID', 'varchar(1024)'),
>>>>>>>>>> x.value('ProductRef_ID', 'varchar(1024)'),
>>>>>>>>>> x.value('ProductRef_URL', 'varchar(1024)'),
>>>>>>>>>> x.value 'ProductRef_Name', 'varchar(1024)'),
>>>>>>>>>> x.value('ItemsSold', 'varchar(1024)'),
>>>>>>>>>> x.value('Orders', 'varchar(1024)'),
>>>>>>>>>> x.value('Revenue', 'varchar(1024)'),
>>>>>>>>>> x.value('PageViews', 'varchar(1024)'),
>>>>>>>>>> x.value('Item', 'varchar(1024)'),
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> FROM @xml.nodes('/Products/Product') x;
>>>>>>>>>>
>>>>>>>>>> Thank you
>>>>>>>>>> Dee
>>>>>>>>>>
>>>>>>>>>> "Dee" wrote:
>>>>>>>>>>

>>>>>>>>>>> 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:
>>>>>>>>>>>

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

>>>>>>>>>>>>>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
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> "Mike C#" wrote:
>>>>>>>>>>>>>

>>>>>>>>>>>>>>
>>>>>>>>>>>>>> "Dee" discussions.microsoft.com> wrote in message
>>>>>>>>>>>>>> news:B13371F0-436D-4E36-92B4-792CF1C90FF8@microsoft.com...

>>>>>>>>>>>>>>> Thanks Mike,
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Will this work if the access data is set up mostly
>>>>>>>>>>>>>>> in
>>>>>>>>>>>>>>> querries?
>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>
>>>>>>>>>>>>>> If you set up a linked server on the SQL side or as
>>>>>>>>>>>>>> linked
>>>>>>>>>>>>>> tabels
>>>>>>>>>>>>>> on the
>>>>>>>>>>>>>> Access side the DBMS (SQL or Access) will see the
>>>>>>>>>>>>>> other's
>>>>>>>>>>>>>> tables
>>>>>>>>>>>>>> as local
>>>>>>>>>>>>>> tables. So you can query them as if they were local
>>>>>>>>>>>>>> tables.
>>>>>>>>>>>>>> Not
>>>>>>>>>>>>>> sure if
>>>>>>>>>>>>>> that's the answer you're looking for, because I really
>>>>>>>>>>>>>> don't
>>>>>>>>>>>>>> understand
>>>>>>>>>>>>>> the
>>>>>>>>>>>>>> question...
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> There is a performance penalty I forgot to mention
>>>>>>>>>>>>>> previously
>>>>>>>>>>>>>> when
>>>>>>>>>>>>>> you do
>>>>>>>>>>>>>> this. For instance, on Access if you perform a join to
>>>>>>>>>>>>>> a
>>>>>>>>>>>>>> linked
>>>>>>>>>>>>>> table the