Extra xml node

This is in sql 2005
I have a query that is returning a set of rows each with one xml field
row1 -<apple></apple>
row3 -<orange></orange>
row2-<grape></grape>
I want the output to look like this
<fruits>
<apple></apple>
<orange></orange>
<grape></grape>
</fruits>
However when I use FOR XML AUTO, root(''Fruits'')
<fruits>
<fruit><apple></apple></fruit>
<fruit><orange></orange></fruit>
<fruit> <grape></grape></fruit>
</fruits>
How do I remove the extra fruit element?
ENDHello Hyper,
Try using a FOR XML PATH query instead, ala for xml path (''),root('fruits')
,type
Thanks!
Kent

> This is in sql 2005
> I have a query that is returning a set of rows each with one xml field
> row1 -<apple></apple>
> row3 -<orange></orange>
> row2-<grape></grape>
> I want the output to look like this
> <fruits>
> <apple></apple>
> <orange></orange>
> <grape></grape>
> </fruits>
> However when I use FOR XML AUTO, root(''Fruits'')
> <fruits>
> <fruit><apple></apple></fruit>
> <fruit><orange></orange></fruit>
> <fruit> <grape></grape></fruit>
> </fruits>
> How do I remove the extra fruit element?
> END
>
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

extended character search

Is there an easy way to perform a SELECT where you have

blah LIKE 'asdf'

but instead of just returning all the asdf's, it also looks for sdf,
sdf, sdf, etc?Right now, this is what I'm doing: replacing each accent letter (a, e,
i, etc) with a string of possible accents. For example

A search for 'GONCALTRONICA' actually searches with:

'G[o][n][c][a]LTR[o][n][i][c][a]'

and returns the correct row with 'GONALTRNICA' in the field. I'm
thinking there has to be a better way to have a case insensitive
search. Maybe an option somewhere?

Thanks|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106163716.326409.20730@.c13g2000cwb.googlegro ups.com...
> Right now, this is what I'm doing: replacing each accent letter (a, e,
> i, etc) with a string of possible accents. For example
> A search for 'GONCALTRONICA' actually searches with:
> 'G[o][n][c][a]LTR[o][n][i][c][a]'
> and returns the correct row with 'GONALTRNICA' in the field. I'm
> thinking there has to be a better way to have a case insensitive
> search. Maybe an option somewhere?
> Thanks

You can specify an accent-insensitive collation in your queries:

create table #pep (col1 nvarchar(100))
insert into #pep select 'GONALTRNICA'

-- Returns 0 rows
select * from #pep
where col1 = 'GONCALTRONICA'
-- Returns 1 row
select * from #pep
where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI

Simon|||I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
I am using an out of date version of sql server that doesn't support
COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

> You can specify an accent-insensitive collation in your queries:
> create table #pep (col1 nvarchar(100))
> insert into #pep select 'GONALTRNICA'
> -- Returns 0 rows
> select * from #pep
> where col1 = 'GONCALTRONICA'
> -- Returns 1 row
> select * from #pep
> where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI
>
> Simon|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106165927.837051.321340@.f14g2000cwb.googlegr oups.com...
> I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
> I am using an out of date version of sql server that doesn't support
> COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

COLLATE is only available in SQL 2000 - please always mention which version
you have (and to be fair, I shouldn't have assumed you had 2000). In SQL 7,
the sort order is fixed at install time, so you would have to rebuild the
master database to change it.

If it's important enough to you, it might be worth rebuilding (but of course
you run the risk of breaking other code), setting up an additional MSSQL
installation for insensitive searches, or upgrading to 2000, otherwise
you're probably stuck with writing code as you've already done. Fulltext
searching is always case and accent sensitive, so unfortunately that's not
an option either.

By the way, your build version indicates you haven't installed any
servicepacks (SP4 is the latest one for SQL 7).

Simon

Exporting to Excel - File gets too big

Hi,

I have a report returning about 50000 rows, when i export this into excel it takes a few minutes and the file size is about 13MB, When i try to open up a 13mb file it is so slow...it is better for me to execute the dataset in SQL analyser and copy the results directly into excel whereby the file is 8mb and opens up also instantly...

My exported version is just data and no graphics however the page appears to be ''white'' although i set the fill in excel to transparent...maybe this is making the file hard to open...

Anybody have problems with exporting to excel and actually able to use it without running into long delays due to the file size...what can i do to fix this

thanks

Wait for SQL Server 2008 apparently.

One of the biggest resource hogs is output to Excel. One of the whitepapers suggests 2x-3x Ram (ratio to dataset size) is required for Excel gen.

Otherwise, set it on a schedule and email the report when it is done.

Exporting to Excel - File gets too big

Hi,

I have a report returning about 50000 rows, when i export this into excel it takes a few minutes and the file size is about 13MB, When i try to open up a 13mb file it is so slow...it is better for me to execute the dataset in SQL analyser and copy the results directly into excel whereby the file is 8mb and opens up also instantly...

My exported version is just data and no graphics however the page appears to be ''white'' although i set the fill in excel to transparent...maybe this is making the file hard to open...

Anybody have problems with exporting to excel and actually able to use it without running into long delays due to the file size...what can i do to fix this

thanks

Wait for SQL Server 2008 apparently.

One of the biggest resource hogs is output to Excel. One of the whitepapers suggests 2x-3x Ram (ratio to dataset size) is required for Excel gen.

Otherwise, set it on a schedule and email the report when it is done.