Menu
I want to alias the output of the following:
-- Test table with some rubbish data
DECLARE @Test TABLE
(
Names [varchar](20)
)
INSERT INTO @Test
SELECT 'Simon'
-- Query returns but with XML_<GUID> alias
SELECT
Names
FROM
@Test t
FOR XML PATH ('Test')
So rather than a column header of XML_GUID I want to give it an alias of say 'Test' for sake of argument. I can't seem to get it. Anyone know how? I tried following an example from here: http://social.msdn.microsoft.com/Forums/nl/sqlxml/thread/1605c722-6388-40ff-9ab5-a3817a1db81f but I can't seem to get it to return. I always run into the error that says the is no name for column 1.
Any help appreciated.
Thanks,
Simon
742
We can use FOR XML PATH to prepare a comma-separated string from the existing data. Let's create an Authors table and insert a few records into it. In the data, we can see we have an ID column and the AuthorName column. If we just select the records, it gives the output in the following format.
In a FOR XML clause, you specify one of these modes: RAW. AUTO. EXPLICIT.
The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
Make it a subquery:
select (
SELECT
Names
FROM
@Test t
FOR XML PATH ('Test'),TYPE) as Test
Subqueries produce values but never provide a name for a column. I also specified ,TYPE because otherwise it forces a conversion to varchar(max) on the result, whereas you presumably want to keep it as xml.
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
