CODEWITHSUNDEEP
sqlsql-server-2012
OverflowStack
OverflowStack

Reputation: 259

SQL Concatenate 2 large TEXT rows from the datatype TEXT

I am struggling to achieve the following:

In my cte I have 2 (or more) large text strings. I want to concatenate these into one row instead of multiple rows.

Here under is the simplified version. However with the for xml it messes up...

Any ideas or approaches? 

WITH cte AS
(
SELECT CAST('<XML></XML>' as text) as Result
UNION ALL
SELECT CAST('<XML AGAIN></XML AGAIN' as text) as Result
)


SELECT Result 
FROM cte p2
FOR XML PATH('')

Upvotes: 0

Views: 67

Answers (1)

Code Different
Code Different

Reputation: 93191

You probably see special characters like brackets (< and >) encoded as HTML entities.

Add a TYPE directive to encode/decode it properly:

WITH cte1 AS
(
SELECT CAST('<XML></XML>' as text) as Result
UNION ALL
SELECT CAST('<XML AGAIN></XML AGAIN>' as text) as Result
), cte2 (XMLResult) AS
(
SELECT Result
FROM cte1
FOR XML PATH(''), TYPE
)

SELECT XMLResult.value('.','nvarchar(max)')
FROM cte2

Upvotes: 1

Related Questions