0

I have MyTable structured as below:

ColumnA | ColumnB
-----------------
ValueA  | ValueB

And would like to produce XML like below:

<ValueA>ValueB</ValueA>

This doesn't work but it's the closest I get. I get a syntax error; apparently you can't use a column value in the ROOT function. Any suggestions are greatly appreciated.

SELECT ColumnB as "data()"
FROM MyTable
FOR XML PATH (''), ROOT(ColumnA)
Mike
  • 620
  • 11
  • 16

1 Answers1

0

A simple solution is to create a string containing your xml concatenating the columns' values and then casting it to xml:

--create table variable to hold sample data 
declare @MyTable table( ColumnA nvarchar(50),ColumnB nvarchar(50) ) 

--populate sample data 
insert into @MyTable select 'ValueA', 'ValueB'

select * from @MyTable 

SELECT cast('<' + ColumnA + '>' + ColumnB + '</' + ColumnA + '>'  as xml)
FROM @MyTable

This is the result:

enter image description here

Andrea
  • 11,801
  • 17
  • 65
  • 72
  • This is okay, but you must be aware, that this will break, if `ColumnB` includes forbidden characters. The linked answer shows a more secure solution. – Shnugo Jan 03 '18 at 19:07