Help with XQuery

I'm trying to do my "Hello World" in XQuery and running into problems. Maybe you can advise.

DECLARE @.MyXML xml

SET @.MyXML = '<?xml version="1.0" encoding="ISO-8859-1" ?>

<bookstore><book category="COOKING">

<title lang="en">Everyday Italian</title>

<author>Giada De Laurentiis</author>

<year>2005</year>

<price>30.00</price>

</book><book category="CHILDREN">

<title lang="en">Harry Potter</title>

<author>J K. Rowling</author>

<year>2005</year>

<price>29.99</price>

</book><book category="WEB">

<title lang="en">XQuery Kick Start</title>

<author>James McGovern</author>

<author>Per Bothner</author>

<author>Kurt Cagle</author>

<author>James Linn</author>

<author>Vaidyanathan Nagarajan</author>

<year>2003</year>

<price>49.99</price>

</book><book category="WEB">

<title lang="en">Learning XML</title>

<author>Erik T. Ray</author>

<year>2003</year>

<price>39.95</price>

</book>

</bookstore>'

When I run this query in sql 2005 Query Editor I get the error message:

Msg 9332, Level 16, State 1, Line 41

XQuery [query()]: Syntax error near 'data', expected 'where', '(stable) order by' or 'return'.

SELECT @.myXml.query('

<booklist>

{

for $p in /bookstore/

where data($p/@.price) > 30.00

order by $p/title[1]

return $p/@.price

}

</booklist>

Any help appreciated,

Barkingdog

I assume you want to list all the book price > 30.00, order by book's title. If so, here is the query

SELECT @.myXml.query('

<booklist>

{

for $p in /bookstore/book

where data($p/price) > 30.00

order by $p/title[1]

return $p/price

}

</booklist>')

Help with WHILE loop in a cursor

I have a cursor within a cursor which is like
Declare vendor_cursor cursor for
select distinct top 10 vendor_name from event_feed_view
Where vendor_id = @.vendor_id
Open vendor_cursor
Fetch Next from vendor_Cursor into @.vendor_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.vendor_feed = '<g:vendor>'+@.vendor_name+'</g:vendor>'
insert into temp_event_feed(xml_data) values (@.vendor_feed)
Fetch Next from vendor_Cursor into @.vendor_name
END
Close vendor_Cursor
Deallocate vendor_Cursor
The result I get here is printed in XML which is like
'<g:vendor>'+SHAWN M+'</g:vendor>'
'<g:vendor>'+MICHAEL L+'</g:vendor>'
'<g:vendor>'+DAWN K+'</g:vendor>'
'<g:vendor>'+LISA S+'</g:vendor>' and so on till 10
Since this is HTML i need my data to be in this format
'<g:vendor>'+SHAWN M+'</g:vendor>'
'<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
'<custom atribute: vendor2>'+DAWN K+<custom atribute: vendor2>
'<custom atribute: vendor3>'+LISA S+<custom atribute: vendor3>
till 10. There can be 10 or less or more vendors in the list
But I want only 10 in my HTML and the format should be like mentions
So I need to create a loop like do while count <=10
and create this html i would have to create a case if count = 1
then use this format:
<g:vendor>'+SHAWN M+'</g:vendor>'
if count is >1
then use the other format
'<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
and print 1 after vendor if count is 2, print 2 after vendor if count
is 3.
I hope this would be clear what I am looking for. Need to pout couple
of loops in there any suggesstion on this.Hi
Your TOP 10 clause should limit the number of rows returned to be 10
distinct vendors but you could try something like:
DECLARE @.cnt int
DECLARE vendor_cursor CURSOR FOR
SELECT DISTINCT TOP 10 vendor_name
FROM event_feed_view
WHERE vendor_id = @.vendor_id
OPEN vendor_cursor
FETCH NEXT FROM vendor_Cursor INTO @.vendor_name
SET @.cnt = 0
WHILE @.@.FETCH_STATUS = 0 AND @.cnt < 10
BEGIN
SET @.vendor_feed = CASE @.cnt WHEN 0 THEN
'<g:vendor>'+@.vendor_name+'</g:vendor>'
ELSE '<custom atribute: vendor' + CAST(@.cnt as varchar(2)) +
'>'+@.vendor_name+'</custom atribute: vendor' + CAST(@.cnt as varchar(2)) + '>'
END
INSERT INTO temp_event_feed(xml_data) VALUES (@.vendor_feed)
FETCH NEXT FROM vendor_Cursor INTO @.vendor_name
SET @.cnt = @.cnt + 1
END
CLOSE vendor_Cursor
DEALLOCATE vendor_Cursor
If you want to include '+' characters in the element value change it to:
'<g:vendor>+'+@.vendor_name+'+</g:vendor>'
John
"VJ" wrote:
> I have a cursor within a cursor which is like
> Declare vendor_cursor cursor for
> select distinct top 10 vendor_name from event_feed_view
> Where vendor_id = @.vendor_id
> Open vendor_cursor
> Fetch Next from vendor_Cursor into @.vendor_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.vendor_feed = '<g:vendor>'+@.vendor_name+'</g:vendor>'
> insert into temp_event_feed(xml_data) values (@.vendor_feed)
> Fetch Next from vendor_Cursor into @.vendor_name
> END
> Close vendor_Cursor
> Deallocate vendor_Cursor
>
> The result I get here is printed in XML which is like
>
> '<g:vendor>'+SHAWN M+'</g:vendor>'
> '<g:vendor>'+MICHAEL L+'</g:vendor>'
> '<g:vendor>'+DAWN K+'</g:vendor>'
> '<g:vendor>'+LISA S+'</g:vendor>' and so on till 10
> Since this is HTML i need my data to be in this format
>
> '<g:vendor>'+SHAWN M+'</g:vendor>'
> '<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
> '<custom atribute: vendor2>'+DAWN K+<custom atribute: vendor2>
> '<custom atribute: vendor3>'+LISA S+<custom atribute: vendor3>
> till 10. There can be 10 or less or more vendors in the list
> But I want only 10 in my HTML and the format should be like mentions
> So I need to create a loop like do while count <=10
> and create this html i would have to create a case if count = 1
> then use this format:
> <g:vendor>'+SHAWN M+'</g:vendor>'
> if count is >1
> then use the other format
> '<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
>
> and print 1 after vendor if count is 2, print 2 after vendor if count
> is 3.
> I hope this would be clear what I am looking for. Need to pout couple
> of loops in there any suggesstion on this.
>

Help With Variable Containing Datetime

HI,

I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.

DECLARE @.DATE NVARCHAR(100)
SET @.DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @.DATE)

WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.

BUT,

WHEN I RUN A VARIABLE CONTAINING DATETIME,

DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + @.DATE)

IT GIVES ME AN ERROR :-

"Line 1: Incorrect syntax near '12'. "

IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.Try this:
DECLARE @.DATE DATETIME

SET @.DATE = GETDATE()
EXEC ('SELECT ' + ''''+@.DATE+'''')

Harshal.|||Hi,

Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.|||And what about this approach:

declare @.date datetime
set @.date = getdate()
select @.date

Greetz,
DePrins
;)|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.

Try this:
DECLARE @.DATE DATETIME,
@.Dt_Dsc Varchar(50),
@.SQL varchar(200)

SET @.DATE = GETDATE()
Set @.Dt_Ddc = Replace(Cast(Left(@.Date , 11) AS varchar(50)),' ','_')
Set @.SQL = 'Select ' + @.Dt_Dsc

Exec (@.SQL)

Gil|||In order to prevent you from tearing out your hair later, I'd like to strongly suggest that you format your dates differently and use them as a prefix rather than a suffix on your table names. If you format the prefix as E20040720 instead of 20_july2004, you won't have problems with 12_dec2000 sorting between 04_jul2004 and 20_may2010! If you use a prefix instead of a suffix, all of your extract tables will sort together by date of extract when you display table names sorted alphabetically. You can use:DECLARE @.prefix VARCHAR(10)
SET @.prefix = 'E' + Replace(Convert(VARCHAR(10), GetDate(), 120), '-', '')Note that I added a letter before the digits, just to make it easier to work with the tables going forward. Sometimes it gets messy trying to cope with table names that start with a digit.

-PatP|||Hi,

Thanks Pat And Glubstein That Was Wonderful Solved Many Of My Problems And Saved Many Headaches.

Thanks Once Again