for-xml

Can't set output of 'WITH XMLNAMESPACES...FOR XML PATH' to a variable?

I have a query like the following: ;WITH XMLNAMESPACES ( DEFAULT 'http://www.somewhere.com') SELECT ( 'SOMETHING' ) FOR XML PATH('RootNode'), TYPE Running this works fine. However, I run into troubles when I try to set the XML output to a variable like this: DECLARE @MYXML AS XML SELECT @MYXML = ( ;WITH XMLNAMESPACES ( DEFAULT 'http...

How can I get Sql Server 2K8 R2 to return this xml without resorting to compatability mode

We are in the process of upgrading our sql server to 2K8 R2 and the output of a FOR XML AUTO query has changed. The query outputs columns from three tables The resultset returns three rows which each column is identical bar the last two columns from the third table. the results used to show as below <element1 myval="Test"> <elemen...