May 27, 2003 at 5:48 am
try the following
use northwind
go
dbcc traceon (257)
select * from customers where customerid = 'ALFKI' for xml auto, elements
While in a SP is it possible to put the result of this select statement into a variable and if so how can I do it ? because I have been unsuccessful so far
May 29, 2003 at 8:52 am
There are two answers to your question.
1. It can't be done with "for xml" (because t-sql won't let you do anything with the output.
2. It can be done by faking a "for xml" statement.
use northwind
go
dbcc traceon (257)
--instead of
select CustomerId, CompanyName from customers where customerid = 'ALFKI' for xml auto, elements
--do this
declare @XML nvarchar(4000)
set @XML=(select '<customers><CustomerId>' + customerId + '</CustomerId><CompanyName>' + CompanyName + '</CompanyName></customers>' from customers where customerid = 'ALFKI' )
print @XML
Both commands seemingly create the same output but the second can actually be used in t-sql.
I think this solves another of your posts as well.
June 4, 2003 at 2:10 am
OK, I will then just use it as a temporary solution.I never thought op doing it like that.Thanks for you help.
Only disadvantage is that I will have to hardcode the columns in the select and 2ndly change the script everytime table changes are made in the db.
But ok, thanks again
June 6, 2003 at 2:52 am
Well if you want column names you could use sysobjects and syscolumns like
SELECT syscolumns.*
FROM
sysobjects
join syscolumns on sysobjects.[id]=syscolumns.[id]
where sysobjects.[name]=<tablename>
bung the output into a temp table, loop round from 1 to @@rowcount (no need for a cursor if you put an identity field on the temp table) building up your string as you go.
Hope that helps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply