"for xml raw, element" output format

This query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml raw('theme'), elements
gives me this output:
<theme>
<themekey>airborne geophysical survey</themekey>
</theme>
<theme>
<themekey>airborne geophysics</themekey>
</theme>
<theme>
<themekey>diamonds</themekey>
</theme>
<theme>
<themekey>drilling</themekey>
</theme>
<theme>
<themekey>geochemical survey</themekey>
</theme>
<theme>
<themekey>geology</themekey>
</theme>
How do I get the format to look as follows:
<theme>
<themekey>airborne geophysical survey</themekey>
<themekey>airborne geophysics</themekey>
<themekey>diamonds</themekey>
<themekey>drilling</themekey>
<themekey>geochemical survey</themekey>
<themekey>geology</themekey>
</theme>
Thanks,
Lee AnneYou did not mention the version of SQL Server (is it 2000 or 2005).
In SQL Server 2005 you can query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml path(''), root('theme')
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Ok, that's SQL Server 2005 :-) I should look at given query more carefully
:-)
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Thank you, that was exactly what I needed!!!
Lee Anne
"Pawel Potasinski" wrote:

> Ok, that's SQL Server 2005 :-) I should look at given query more carefully
> :-)
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa3 w
> wiadomo?ci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
>
>

"for xml raw, element" output format

This query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml raw('theme'), elements
gives me this output:
<theme>
<themekey>airborne geophysical survey</themekey>
</theme>
<theme>
<themekey>airborne geophysics</themekey>
</theme>
<theme>
<themekey>diamonds</themekey>
</theme>
<theme>
<themekey>drilling</themekey>
</theme>
<theme>
<themekey>geochemical survey</themekey>
</theme>
<theme>
<themekey>geology</themekey>
</theme>
How do I get the format to look as follows:
<theme>
<themekey>airborne geophysical survey</themekey>
<themekey>airborne geophysics</themekey>
<themekey>diamonds</themekey>
<themekey>drilling</themekey>
<themekey>geochemical survey</themekey>
<themekey>geology</themekey>
</theme>
Thanks,
Lee Anne
You did not mention the version of SQL Server (is it 2000 or 2005).
In SQL Server 2005 you can query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml path(''), root('theme')
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne
|||Ok, that's SQL Server 2005 :-) I should look at given query more carefully
:-)
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne
|||Thank you, that was exactly what I needed!!!
Lee Anne
"Pawel Potasinski" wrote:

> Ok, that's SQL Server 2005 :-) I should look at given query more carefully
> :-)
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa3 w
> wiadomo?ci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
>
>

"expanding" updates

Hi,
i need to "expand" a raw datatable like

1, 'Harry', 'London'
2, 'Peter', null
3, null, 'New York'

into this format

1, 'Harry', 'London'
2, 'Peter', 'London'
3, 'Peter', 'New York'

so, for several columns, null values should be replaced with the values of the previous (non-null) row. the raw data is bulk-inserted and cannot be modified before.

is there anything other (faster!) than using a cursor ?
i like to have on single update command to do the job.does each row in the table have a unique sequential key?|||Originally posted by Paul Young
does each row in the table have a unique sequential key?

yes, the first column is an identity|||why not join the table to its self based on the identity column?

select *
from yourbcptable ybt1
join yourbcptable ybt2 on ybt1.IdentityColumn - 1 = ybt2.IdentityColumn
where ybt1.IdentityColumn > 1

This assumes your identity seed = 1|||yes, the identity seed is 1, so your select works,

but what about multiple null rows in order like

1, 'Peter', 'london'
2, 'Markus', null
3, 'Mary', null

for row 3 the row (id-1) will be null as well?|||either a row at a time approach or run the update till you no longer have nulls or wrap everything up in a stored procedure.

probably should have added more to the where caluse

where ybt1.IdentityColumn > 1
and ytb1.col1 is null or ybt1.col2 is null

BTW, I would NOT use a CURSOR as these tend to be resource intensive.|||i got closer. the following update will nearly do the job, but the update order needs to be by the identity column. is there a way to set the order for updates ?

update mytable set
@.Column1 = Column1 = isnull( Column1, @.Column1 ),
@.Column2 = Column2 = isnull( Column2, @.Column2 )

this will set column1 to column1 (keep the value) when it is not null or will set column1=@.column1, which holds then "not null"-value of the rows before. works fine - but only when processed in correct order !|||No guaranties on the order. SQL Server will process in the most efficient manner.

You might try setting a clustered index on the key, but I would check the results very closely.|||--create table T (a int identity, N varchar(10), C varchar(10))
/*
delete from T
insert into T (n,c) select 'Harry','London'
insert into T (n,c) select 'Peter',null
insert into T (n,c) select null,'New York'
*/
select * from T
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1
select * from T|||Originally posted by kukuk
--create table T (a int identity, N varchar(10), C varchar(10))
/*
delete from T
insert into T (n,c) select 'Harry','London'
insert into T (n,c) select 'Peter',null
insert into T (n,c) select null,'New York'
*/
select * from T
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1
select * from T

correct - that's what we discussed before,
but what about multiple null rows in order like

1, 'Peter', 'london'
2, 'Markus', null
3, 'Mary', null

for row 3 the row (id-1) will be null as well?
there might be many null-rows after one data-row.|||:)

while exists (select * from T where a>1 and (C is null or N is null) )
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1

(will work only if the first row does not contain nulls)

or

update x set
N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
from T as x

(expensive one)|||Originally posted by kukuk
:)

while exists (select * from T where a>1 and (C is null or N is null) )
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1

(will work only if the first row does not contain nulls)

or

update x set
N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
from T as x

(expensive one)

the first one seems to be the one ...
i'll give it a try and come back later|||hi kukuk,

i think i got it:

declare @.A int, @.B varchar(20), @.C varchar(20)

select top 1 @.A = A from Tbl order by ID
while @.@.RowCount>0 begin
update Tbl
set @.B= B= isnull( B, @.B ),
@.C= C= isnull( C, @.C ),
@.A=A+1
from Tbl
where A=@.A
end

its a mix of your first select and my variable-version. your select works fine too, but it is much more expensive than the one above.

thanks to all for your time
markus|||Yes but the number of iterations will be equal to nuimber of rows in the table.|||Originally posted by kukuk
Yes but the number of iterations will be equal to nuimber of rows in the table.

true, but i don't care as long as it runs much faster.

"DROP INDEX failed because the following SET options have incorrect settings: 'ARITHABORT

Hi,
I've got a big import routine that brings a lot of data in raw tables into
another database - the routine is about 2000 lines long. I have a DTS
package that first brings the data in from CSV files and other sources, then
it runs this sproc to bring the data into the other database, then finally
it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
sproc in the target database which drops all indexes on the target database,
start a transaction, bring all of the data in, then EXEC another sproc on
the target database to recreate the indexes again. Like I said, it's a
monster! :)
This sproc worked flawlessly for about six months, but now when I run the
DTS package it fails on the sproc step with the error mentioned in the
Subject line. The table on which this is failing has a clustered unique
index and 8 other standard indexes, but there are no indexes on calculated
columns or indexed views or anything like that. I am able to run the sproc
on the data from Query Analyzer with no errors, and I have tried DROPping
INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
Can anybody help out with this?
Thanks, JimDo you have any hypothetical indexes created by the ITW ?
select * from sysindexes where name like 'hind_%'
If you have no "real" indexes on computed columns or views its woeth
dropping any hypothetical ones and seeing it that helps
It may be worth explicitly setting the correct set options in your sproc
anyway to be on the safe side
set ANSI_PADDING,ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,ARITHABORT,
QUOTED_IDENTIFIER,ANSI_NULLS on
set NUMERIC_ROUNDABORT off
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim" <jim@.x.com> wrote in message
news:erLkiH%23JEHA.3276@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a big import routine that brings a lot of data in raw tables into
> another database - the routine is about 2000 lines long. I have a DTS
> package that first brings the data in from CSV files and other sources,
then
> it runs this sproc to bring the data into the other database, then finally
> it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
> sproc in the target database which drops all indexes on the target
database,
> start a transaction, bring all of the data in, then EXEC another sproc on
> the target database to recreate the indexes again. Like I said, it's a
> monster! :)
> This sproc worked flawlessly for about six months, but now when I run the
> DTS package it fails on the sproc step with the error mentioned in the
> Subject line. The table on which this is failing has a clustered unique
> index and 8 other standard indexes, but there are no indexes on calculated
> columns or indexed views or anything like that. I am able to run the
sproc
> on the data from Query Analyzer with no errors, and I have tried DROPping
> INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
> Can anybody help out with this?
> Thanks, Jim
>