Removing XML tags from FOR XML PATH

Question

I am getting data from a sql string to make an XML file, here is my SQL

SELECT [data] AS data
FROM MyTable 
WHERE [Status] = '1' 
FOR XML PATH('DataLocation')

And here is my XML return

<DataLocation>
  <data>String</data>
</DataLocation>

The problem I have is I want to have the tags DataLocation but not the tag data, I just want to show the String value.

Where am I going wrong?

Answer 1

There are several ways which lead to the same result. All of them have there tiny goods and bads:

DECLARE @tbl TABLE(Data NVARCHAR(100), [Status] INT)
INSERT INTO @tbl(Data, [Status]) VALUES 
 ('test 1 with 1', 1)
,('test 1 with 2', 2)
,('test 2 with 1', 1);

SELECT [data] AS [*]
FROM @tbl
WHERE [Status] = 1
FOR XML PATH('DataLocation');

SELECT [data] AS [node()]
FROM @tbl
WHERE [Status] = 1
FOR XML PATH('DataLocation');

SELECT '' + [data] --Any kind of computation results in "no caption name"
FROM @tbl
WHERE [Status] = 1
FOR XML PATH('DataLocation') 

SELECT [data] AS DataLocation
FROM @tbl
WHERE [Status] = 1
FOR XML PATH('') 

Little background: If there is a column name it will be used. So you have to find a way do get rid of this column name, or replace it with the one you want.

Answer 2

The result will be wrapped in an xml-element with the name of the column, which in turn will be wrapped in the value of XML PATH.

This should work:

SELECT [data] as 'DataLocation'
FROM MyTable
WHERE [Status] = '1' 
FOR XML PATH('')