For XML Path problem?

I have a stored procedure that is to create an XML file, once the temporary
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.
Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.