Re: how to insert xml into sql 2005
- From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 28 Feb 2008 13:47:37 -0800
Please add a [1] to address the error message 2389 as in the following example to every value method where you refer to an element in the path expression:
x.value('Description[1]', 'varchar(1024)')
For performance reasons, I suggest to also add the text() access as in:
x.value('(Description/text())[1]', 'varchar(1024)')
Best regards
Michael
"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.
> >> > > >>
> >> > > >>
> >> > > >>
> >> > >
> >> > >
> >> > >
>
>
>
.
- Prev by Date: Re: Query an xml column
- Next by Date: XQUERY big problem
- Previous by thread: 3-way join -> xml tree
- Next by thread: XQUERY big problem
- Index(es):
Relevant Pages
|
