Reputation: 10064
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
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
Reputation: 204934
SELECT '<XMLData>' as xmlResult
UNION ALL
SELECT '<Data1>'+data1+'</Data1><Data2>'+data2+'</Data2>' FROM table
UNION ALL
SELECT '</XMLData>'
Upvotes: 5