Re: how to insert xml into sql 2005

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: search xml document
    ... Unfortunately, SQL Server 2000 Full-text Search or SQL FTS for short, was ... Service and using either Microsoft's XML IFilter or other 3rd Party ...
    (microsoft.public.sqlserver.fulltext)
  • Re: how to insert xml into sql 2005
    ... DECLARE @xml XML; ... like "pass-through" queries. ... I would recommend loading your Access data into tables located on SQL Server ...
    (microsoft.public.sqlserver.xml)
  • Re: how to insert xml into sql 2005
    ... DECLARE @xml XML; ... Access side the DBMS (SQL or Access) will see the other's tables as local ... Jet engine pulls the entire table from SQL Server and performs the join ... I would recommend loading your Access data into tables located on SQL ...
    (microsoft.public.sqlserver.xml)
  • Re: .NET Funktionen zu XML->SQL-Server
    ... die mir aus einem XML-Schema eine Tabelle in einer Datenbank ... Ab SQL Server 2005 werden auch Schemas ... XML Best Practices for Microsoft SQL Server 2005 ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: Is there a way to convert ms sql 2008 tables to XML files?
    ... After looking at the code I made it work, since I removed 2 nVARCHARcolumns from the SQL statement where it is used as an RTF. ... I have the 2 XML files the bad and the good which are below: ... Verdana;}{\f1\fnil\fcharset0 Microsoft Sans ... Here is the actual string copied from the ms sql 2008 which works fine; ...
    (microsoft.public.dotnet.framework.aspnet)
Loading