CODEWITHSUNDEEP
sqlxml
Setsuna F. Seiei
Setsuna F. Seiei

Reputation: 302

No column name was specified for column error

I am trying to remove trailing commas from a data which has been concatenated using XML path.

When I nest the XML Path query in the LEFT function query, I get all sorts of errors.

It gives me

Incorrect syntax error nest ')'

pointing to the closing bracket before [tb].

When I use alias for the inner query, it gives me

no column was specified

This is my query

select 
    LEFT(tb.col1, LEN(tb.col1) - 2)
from 
    (select 
        col1 + ', '
     from 
        tabSample 
     where 
        tabId = XXXX
     For XMl Path('') ) [tb]

Any help will be appreciated.

Thanks

Upvotes: 1

Views: 2472

Answers (1)

M.Ali
M.Ali

Reputation: 69524

select STUFF((select  ', ' + col1 
              from tabSample 
              where tabId = XXXX
              For XMl Path(''),TYPE).
              value('.','NVARCHAR(MAX)'),1,2,'')

Add a comma in front of the value and then use STUFF function to remove the very 1st comma. The above example only deals with the leading comma issue.

You probably need to look at the whole query as well.

Upvotes: 3

Related Questions