CODEWITHSUNDEEP
sqlsql-serverxml
user917170
user917170

Reputation: 1641

Decode text from a FOR XML Sql Query

Say I'm using a FOR XML sub query to allow concatenation and sorting, and in addition, the data contains angle brackets:

SELECT STUFF((SELECT ', ' + '<' + NAME + '>'
          FROM   [sys].[login_token]
          ORDER  BY NAME
          FOR XML PATH('')), 1, 2, '') AS Logins;

The output is something like 

&lt;\Everyone&gt;, &lt;BUILTIN\Administrators&gt;, &lt;BUILTIN\Administrators&gt;

How can I have it decoded, that is, something like this:

<\Everyone>, <BUILTIN\Administrators>, <BUILTIN\Administrators>,

?

Upvotes: 4

Views: 2140

Answers (2)

user917170
user917170

Reputation: 1641

You can do a query on the XML and return a converted value!

SELECT STUFF((SELECT ', ' + '<' + NAME + '>'
          FROM   [sys].[login_token]
          ORDER  BY NAME
          FOR XML PATH(''), type).value('.','NVARCHAR(MAX)')
          , 1, 2, '') AS Logins;

You add , type to the FOR XML PATH specification, then a .value with an XQuery expression and data type to convert to! See MSDN Here:

https://msdn.microsoft.com/en-us/library/ms178030.aspx

Upvotes: 5

void
void

Reputation: 7890

you could also use 2 replace functions:

SELECT replace(replace(STUFF((SELECT ', ' + '<' + NAME + '>'
          FROM   [sys].[login_token]
          ORDER  BY NAME
          FOR XML PATH('')), 1, 2, ''),'&lt;','<'),'&gt;','>') AS Logins;

Upvotes: 0

Related Questions