Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Name XML result column of TSQL "for xml explicit"?

I have a query that uses for xml explit to return XML result.

select ... from ... order by [BatchType!1!TypeName], [FormType!2!TypeName], Tag, Parent for xml explicit, root('ClientImages')

But the name of resultant column name is something as cryptic as

alt text

Is there a way to change the column name?


[ANSWER] I had a several nested WITH statements so I have saved the result of query without applying FOR XML EXPLICIT into a temp table @xmlTable and then set the XML EXPLICIT result to an XML then returned it.

declare @xmlResult xml
set @xmlResult =(   
    select  * 
    from    @xmlTable
    for xml explicit, root('ClientImages'))

select  @xmlResult as XmlResult
like image 665
dance2die Avatar asked Oct 14 '09 14:10

dance2die


People also ask

How can I get SQL query results in XML?

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.

How do I change the value of an XML column in SQL?

To update data in an XML column, use the SQL UPDATE statement. Include a WHERE clause when you want to update specific rows. The entire column value will be replaced. The input to the XML column must be a well-formed XML document.

What is use of stuff in SQL Server?

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

What is type in XML Path?

Adding the FOR XML PATH(''), TYPE converts these rows into a single strongly-typed XML text node with the concatenated rows, allowing XML methods like value to be used on the type: select ',' + quotename (C.name) from sys.columns c where c.object_id = OBJECT_ID('dbo.result2') for xml path(''), TYPE.


1 Answers

-- add a select ( first) and type (on the end), and as [something]


select
    ( 
    select 1    as tag,
    null        as parent,
    'algo'      as [nodo!1!attr]
    for xml explicit, type
    ) as [MyDesiredName]



like image 159
guille Avatar answered Nov 16 '22 00:11

guille