Select * from second column ?

  • 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?

  • 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

  • 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

  • Sorry forget to mention that @sql variable value is.

    @sql = 'select [Q1],[A1_COMMENT],[Q2],[A2_COMMENT],[Q3],[A3_COMMENT]'

    Regards

    Arshad

  • 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.

  • 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?

  • 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).

  • 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

  • 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']

  • 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

  • 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

    set @k = @k +1

    End

    declare @sql varchar(1000)

    set @sql='select '+@columnname_to_use+' from Data'

    Exec(@sql)

  • Andrew Gothard (10/17/2008)

    Personally I totally agree - it would seem that quite a few don't, though.

    See what I mean?

  • "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.

    🙂

  • 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?

  • 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