Re: how to insert xml into sql 2005

Mike,

I used the following and only got one column in the table.

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

When I try to open the column there is nothing in it that can be read.

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
    ... 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
    ... DECLARE @xml XML; ... CONVERT(XML, BulkColumn, 2) ... "Dee" wrote: ... SQL Server provides only very limited support for DTDs. ...
    (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)
  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
Loading