October 15, 2008 at 6:00 am
Surely, if you have a column you do not wish to display in an XML Document, and it's always going to be called Id, or C1, or whatever - rather than put together baroque queries do deal with the output, the appropriate place to deal with it would be in your XSL / XSLT?
October 15, 2008 at 6:08 am
I'd completely agree with Andrew. You're unnecessarily loading the server to build this when you could handle it on the client.
PS - names are in bold. You're referencing post levels. I'm not SSC Champion, I'm Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 15, 2008 at 2:32 pm
Hi Matt,
Thank you for your help. I tried your code and it works fine with where clause with sp_executeSql :).
Now again i am stuck with one small thing i.e. how do we print the variable value into the query here is the sample of code based on your code.
-- Extracting the Answers and comments columns only and call it in a variable for Answer XML
declare @column_num int
declare @columnname varchar(max)
declare @tablename varchar(max)
set @column_num=2
set @columnname='';
set @tablename='tblAnswersOutPut'
select @columnname=@columnname+',tao.['+column_name+']' from information_schema.columns
where table_name=@tablename and ordinal_position >= @column_num
declare @sql nvarchar(max)
set @sql= 'select '+substring(+@columnname,2,len(@columnname))
I needed to print @sql variable into the query which is as follows.
(PRINT @sql FOR XML PATH('QUESTIONS'), Type)
FROM #tmp_DataFeed inn LEFT JOIN tblAnswersOutput tao ON inn.answer_id = tao.Answer_id.
Forgive me if its a small stupid Question because i am a newbie :(.
Regards
Arshad
October 15, 2008 at 2:35 pm
Sorry forget to mention that @sql variable value is.
@sql = 'select [Q1],[A1_COMMENT],[Q2],[A2_COMMENT],[Q3],[A3_COMMENT]'
Regards
Arshad
October 16, 2008 at 4:57 pm
SQL should not be the answer to everything.
If you want XML then at some point an XSL transformation should be possible.
Why make a complicated overhead at the SQL end when a simple filter in an XSL transformation would suffice
@*[position() > 1]
OR
@*[name() != 'id']
the @ is the attribute or field name
if you use elements for fields its even simpler.
October 16, 2008 at 6:43 pm
You just want to keep on concatenating that stuff on to @SQL, then you can either print @SQL (and execute it on your own), ro run it through sp_executeSQL.
Add this on to the end:
set @sql=@sql+' FOR XML PATH(''QUESTIONS''), Type)
FROM #tmp_DataFeed inn LEFT JOIN tblAnswersOutput tao ON inn.answer_id = tao.Answer_id'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2008 at 12:34 am
I saw the reply somewhere in the chain.
Try this
Select * into #temptable
from your xml file(not sure about the syntax)
Drop the first column
Then
Select * from #temptable for Xml auto(or raw, which ever suits you).
October 17, 2008 at 4:28 am
OK, now I'm intrigued.
We have two basic options regarding the presentation of data in an XML document, these seem to boil down to a range of complex SQL based solutions putting a strain on the server with associated coding, testing and maintenance issues. Or a simple XSL option.
Could those who seem to consider the first to be the best option explain why? After all - what happens if someone then needs to see the first column - do you then develop a second sp, and if someone else is only interested in columns 2 to 6 yet another? Where do you draw the line in jumping through hoops at the SQL end when there's a totally straightforward international standard which (I reckon) will allow you to achive the result in less time than it takes to read this thread - let alone test the suggestions?
Not aiming to start a flame war here, mind, so please don't bite if you think this is criticism because that's not the aim at all - I'm just wondering, for example, if some of the replies are 'wow, interesting puzzle' related
October 17, 2008 at 4:35 am
Totally agree and if you see my previous post I even showed how easy it is to filter attribute/column.
Choosing the right tool for the job is as much a skill doing the task.
SQL should not be the answer to everything.
If you want XML then at some point an XSL transformation should be possible.
Why make a complicated overhead at the SQL end when a simple filter in an XSL transformation would suffice
@*[position() > 1]
OR
@*[name() != 'id']
October 17, 2008 at 5:01 am
NotManyPoints (10/17/2008)
Totally agree and if you see my previous post I even showed how easy it is to filter attribute/column.Choosing the right tool for the job is as much a skill doing the task.
SQL should not be the answer to everything.
If you want XML then at some point an XSL transformation should be possible.
Why make a complicated overhead at the SQL end when a simple filter in an XSL transformation would suffice
@*[position() > 1]
OR
@*[name() != 'id']
Personally I totally agree - it would seem that quite a few don't, though. Just wondering what their perspective is
October 17, 2008 at 5:11 am
This may work for you:
Replace table data with your tabel
In your select statement you can create a temp table.
declare @column_num int
declare @column_max int
declare @k int
declare @columnname varchar(100)
declare @columnname_to_use varchar(1000)
declare @sqla varchar(100)
-- Get the max number of columns in your table
select @column_max = max(ordinal_position) from information_schema.columns where table_name= 'Data'
set @k = 2 -- Start with column 2
select @columnname=column_name from information_schema.columns
where table_name='Data' and ordinal_position=@k
set @columnname_to_use = @columnname
set @k = 3 -- Continue with column 3
while @k <= @column_max
Begin
select @columnname=column_name from information_schema.columns
where table_name='Data' and ordinal_position=@k
set @columnname_to_use = @columnname_to_use + ',' + @columnname
End
declare @sql varchar(1000)
set @sql='select '+@columnname_to_use+' from Data'
Exec(@sql)
October 17, 2008 at 5:29 am
Andrew Gothard (10/17/2008)
Personally I totally agree - it would seem that quite a few don't, though.
See what I mean?
October 17, 2008 at 6:24 am
"Surely, if you have a column you do not wish to display in an XML Document, and it's always going to be called Id, or C1, or whatever - rather than put together baroque queries do deal with the output, the appropriate place to deal with it would be in your XSL / XSLT? "
Excuse me.
Of course agree on that.
I was off line and did not see why
Arshad wanted to get out the second column etc.!!
If one have a question to a formum like this why not present the problem with
a background "What do I want" and the solution could something else instead of the original
problem.
🙂
October 17, 2008 at 7:13 am
Andrew Gothard (10/17/2008)
OK, now I'm intrigued.We have two basic options regarding the presentation of data in an XML document, these seem to boil down to a range of complex SQL based solutions putting a strain on the server with associated coding, testing and maintenance issues. Or a simple XSL option.
Could those who seem to consider the first to be the best option explain why? After all - what happens if someone then needs to see the first column - do you then develop a second sp, and if someone else is only interested in columns 2 to 6 yet another? Where do you draw the line in jumping through hoops at the SQL end when there's a totally straightforward international standard which (I reckon) will allow you to achive the result in less time than it takes to read this thread - let alone test the suggestions?
Not aiming to start a flame war here, mind, so please don't bite if you think this is criticism because that's not the aim at all - I'm just wondering, for example, if some of the replies are 'wow, interesting puzzle' related
I'd go with "it's in the eye of the beholder". Simple vs complicated essentially comes down to what you're used to. I've done both, and to me - the 2005 options for creating XML are actually easy, especially once you've used them once or twice. It's also a lot more straightforward to me not to have to go build all of the .NET stuff it would take to format the XML exactly as I want it.
As to performance - there's really not much of a perf penalty for adding the FOR XML stuff in, so having SQL do it seemed to be the LESS intense process than having the proc/query run and still have to have something else turn it into the output you want.
That being said - if it's easy for you to do it the other way, go for it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2008 at 9:50 am
Just to fill in some gaps since my last post (which appears to have started a heated debate!)...
When you have an problem with something you fix it with the tools you have available. We rarely (ie:it would take be at least 30 minutes to find one example) use XML here. Thus; i have VERY little XML knowledge or experience. I dont have any opinion that SQL is better than XML or the reverse, I dont have enough knowledge or experience of them both to take the discusson to such a level.
I dont dispute that XML is the better tool for this process and if i ever had the need to do the process that started the post then I would come back here to try and follow the details that have been posted.
I never said my solution was the best either in performance or process, just that it would do what was requested. Does anyone know how many rows of data are involved, how often the process is going to run etc ? If this is a one-off for 5k rows then, for me (thats - FOR ME), it would be quicker to write the SQL, thats the way i work. I cant help it, its just the way I am.
apologies to all involved if i provided a solution that didnt solve anything
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply