Hi,
How to create stored procedure to generate xml for view not for table?

View and table differs in only not affecting the data....so u can create SP / Query to retrieve the data in XML

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

Hi,
See the following query.I want to retrieve data from more than one table.

Select distinct Owner.Name,employee.Id,employee.Name from Owner,employee where Owner.Name=employee.OwnerName and employee.Id=Owner.employeeId for xml auto,root('xml')

This can generate XML and successfully executed.

But if u use the same query while creating view at that time itself it shows error as 'unable to parse the text' and View is not created in SqlServer.Could u tell me the way to use this query to create view

Hi,
I found the solution for this.

Through SqlServer we can create stored Procedure and include that in asp.net by using existing stored Procedure.

If u want the set the node of ur own then the following should be used,

CREATE procedure [dbo].[GetTestMenu]
as SELECT 1 as Tag,
0 as Parent,
MenuName as [MenuMapping!1!MenuName],
NULL as [Category!2!Id],
NULL as [Category!2!Name]

FROM MenuMapping
UNION
SELECT
2 as tag,
1 as parent,
MenuName,
Id,
[Name]from Category for xml explicit,root('menu')
RETURN

Here the xml will be in the following format,
<menu>
<MenuMapping MenuName="Book"/>
<MenuMapping MenuName="CSE"/>
<MenuMapping MenuName="EEE">
<Category Id="21" Name="ControlSystem"/>
<Category Id ="22" Name="Architecture"/>
</MenuMapping>
</menu>