Re: how to insert xml into sql 2005

Just one change:

x.value('Item', 'varchar(1024)'), -- <--- get rid of trailing comma in the
next to last line


"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:821E06AB-18B9-4A09-8BE5-236C815E4C6A@xxxxxxxxxxxxxxxx
Here is the code and I can not find an error any more:

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;

Thanks
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
    ... 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: 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)
  • Re: XML-Speicherung beim SQL Server 2005
    ... > also wenn die XML-Dokumente geparst werden, ... das der SQL Server dafür XQuery einsetzt. ... Struktur von XML. ...
    (microsoft.public.de.sqlserver)
  • Possible?? SQL Svr obtaining data via SOAP/XML from another SQL Sv
    ... SQL servers (one local, one remote through the internet). ... I used VBScript, MSXML, MSSOAP to connect, query, parse and insert data into ... XML" functionality provided in SELECT verb. ...
    (microsoft.public.sqlserver.xml)
  • Re: Implementation of boolean types.
    ... >> Marshall Spight wrote: ... I think of SQL as bringing the 3VL into popularity, ... world and XML will surely play I roll. ... XML doesn't address data management; ...
    (comp.databases.theory)
Loading