CODEWITHSUNDEEP
sqlsql-server
QuestionC
QuestionC

Reputation: 10064

Concatenate select results into one

I need to write a procedure or select where the rows are going to be converted to lines in an XML file.

It's not hard to pull the data...

SELECT '<XMLData'>
SELECT '<Data1>'+data1+'</Data1><Data2>'+data2+'</Data2>' FROM table
SELECT '</XMLData'>

But that's giving me 3 result tables. How do I concatenate them into 1?

Upvotes: 1

Views: 49

Answers (2)

Mike Deluca
Mike Deluca

Reputation: 1210

If you want just one row, you could concatenate them. Casting it as XML will preserve the XML structure in the field.

SELECT CAST(CONCAT('<XMLData>', 
                   '<Data1>'+data1+'</Data1><Data2>'+data2+'</Data2>', 
                   '</XMLData>') as XML) as 'Result 
FROM table

Upvotes: 0

juergen d
juergen d

Reputation: 204934

SELECT '<XMLData>' as xmlResult
UNION ALL
SELECT '<Data1>'+data1+'</Data1><Data2>'+data2+'</Data2>' FROM table
UNION ALL
SELECT '</XMLData>'

Upvotes: 5

Related Questions