Hot answers tagged

6

You could do that using the sql:column function and a Common Table Expression (CTE), something like this: CREATE TABLE #tmp ( col2 XML ) INSERT INTO #tmp ( col2 ) VALUES ( '<Root> <Row> <Rowid>trim this value </Rowid> </Row> </Root>' ) SELECT 'before' s, * FROM #tmp ;WITH cte AS ( SELECT col2, RTRIM(...


3

Based off of your examples queries, this should work out for you (Comments Included): -- works SELECT o1.oname AS [Order Name], (SELECT (STUFF( -- Here is the XML concatenation. (SELECT ', ' + p.pname FROM dbo.products AS p INNER JOIN dbo....


2

Another workaround for this problem is to use set based logic, replacing the CASE expression with a left join (or outer apply) to a reference table (ref in code below), which can be either permanent, temporary or a derived table / CTE. If this is needed in multiple queries and procedures, I'd prefer to have this as permanent table: SELECT ref.result_column ...


1

You have to be very careful. Assuming your query is not restarting and you have a rollback for that SPID. If you restart SQL Server it won't help, because transaction still would have to be rolled back. The problem with that is following: When you run a transaction in multi-CPU environment with not restricted degree of parallelism it most probably will ...



Only top voted, non community-wiki answers of a minimum length are eligible