Re: how to insert xml into sql 2005

Do me a favor, try opening the XML file in Internet Explorer. What result
do you get?

"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:73260B01-419E-4E93-9D56-488BEEE88ADB@xxxxxxxxxxxxxxxx
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" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A87085E2-821A-4CC7-B17A-8FBBAD707C26@xxxxxxxxxxxxxxxx
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" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:94A6DEB3-8BDC-46A5-8891-70E2BB336F5E@xxxxxxxxxxxxxxxx
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" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E9BC5B19-60F1-4512-9B96-5653E3EC3C43@xxxxxxxxxxxxxxxx
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" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B13371F0-436D-4E36-92B4-792CF1C90FF8@xxxxxxxxxxxxxxxx
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
Jet engine pulls the entire table from SQL Server and
performs the
join
itself locally. For large tables this can be a huge
performance
hit, but
for small tables it won't matter too much. There are
ways to
avoid
this,
like "pass-through" queries.

I would recommend loading your Access data into tables
located on
SQL
Server
at some point so you can do all queries and data
comparisons
locally on
the
SQL Server. Access has a nice little Upsizing Wizard
that can
do
the
basic
upgrade for you, although you'll still have to set up
some
sort of
routine
to update the data on a regular basis. OTOH, you may
be in a
situation
where
you're stuck with Access.














.

Relevant Pages

  • Re: how to insert xml into sql 2005
    ... "Dee" wrote: ... "Mike C#" wrote: ... DECLARE @xml XML; ... Jet engine pulls the entire table from SQL Server and ...
    (microsoft.public.sqlserver.xml)
  • Re: how to insert xml into sql 2005
    ... Mike this is what I am getting in the table: ... "Dee" wrote: ... SELECT CONVERT(XML, BulkColumn, 2) ... DECLARE @xml XML; ...
    (microsoft.public.sqlserver.xml)
  • Re: how to insert xml into sql 2005
    ... SQL Server provides only very limited support for DTDs. ... SELECT CONVERT(XML, BulkColumn, 2) ... DECLARE @xml XML; ... "Dee" wrote: ...
    (microsoft.public.sqlserver.xml)
  • Re: how to insert xml into sql 2005
    ... SQL Server provides only very limited support for DTDs. ... SELECT CONVERT(XML, BulkColumn, 2) ... DECLARE @xml XML; ... "Dee" wrote: ...
    (microsoft.public.sqlserver.xml)
  • Re: how to insert xml into sql 2005
    ... DECLARE @xml XML; ... CONVERT(XML, BulkColumn, 2) ... "Dee" wrote: ... SQL Server provides only very limited support for DTDs. ...
    (microsoft.public.sqlserver.xml)
Loading