Retrning XML raw from Stored procedure - (by using dual table)

Hi ,

I have a problem returning the @.CustomerNo from dual for one of my stored procedures. (by the way I do a some manipulations and assign a value to @.CustomerNo everytime this stored procedure runs)

SELECT @.CustomerNo AS CustomerNumber from dual FOR XML RAW

What I figured out is that the reason no values is returned in my XML file is because the dual table does not have any rows. But ,the dual table always have a dummy row by default. Doesnt it.

Alsoif possible please let me know any alternative way to returnthe following XML :

<row>

<CustomerNumber >

value of @.CustomerNo

<CustomerNumber >

</row>

Thanks in advance.

The dual table is an Oracle (shudders) feature.

If you are using SQL Server 2005, you can do the following:

Code Snippet

SELECT @.CustomerNo AS CustomerNumber

FOR XML PATH('row')

--

Peter DeBetta

MVP - SQL Server

http://sqlblog.com

|||Super.. Thanks for the soln.sql