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?

Solution 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.

Solution 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('')

Solution 3

From your comment, what you actually want is a single string value within a DataLocation tag. So all you need is

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

Note that if your table has multiple matching rows, you will get multiple DataLocation tags output; you can use an additional , ROOT('whatever') to surround these tags with a root element.

Solution 4

Maybe you looking for this ?

DECLARE @MyTable TABLE(Data NVARCHAR(256), [Status] NVARCHAR(10))
INSERT INTO @MyTable(Data, [Status])
VALUES ('String', '1'), ('String1', '1'), ('String2', '1'), ('String3', '1'), ('String4', '2') 

SELECT STUFF((SELECT ' ' + Data FROM @MyTable WHERE [Status] = '1' FOR XML PATH('')), 1, 1, '') AS DataLocation FOR XML PATH('')

Result

<DataLocation>String String1 String2 String3</DataLocation>

Solution 5

This thread helped me solve a similar problem; maybe someone else has the same problem:

In the case that the column 'data' is itself an XML datatype, you may find yourself with an additional XML tag that you don't want:

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

Say 'data' contains 'bar'. Then the above query will return:

<DataLocation><data><foo>bar</foo></data></DataLocation>

If you only want the following returned:

<foo>bar</foo>

Then change the query to this:

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

View more solutions