I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.If I put it like
select @.l_variable = (select my sql statement for Auto, Elements)
it gives me syntax error.
I can't do a select into a temp table from my select statement for XML Auto.
I can't use select into as well.
I can't enclose my entire select statment in paranthesis to put it in a table variable.
How do I proceed?|||Here is the actual query. I am trying to get the result of the select statement into the table variable:
declare @.i_Customer_Id int
,@.i_Role_ID int
,@.i_Base_URL varchar(255)
declare @.l_XML_Table table (XML_String varchar (8000))
select @.i_Customer_Id = 10
,@.i_Role_ID = 1
,@.i_Base_URL = 'http://www.NewWebSite.com'
select
MM.Module_Description Topic_Type
,PM.Page_Description Title
,@.i_Base_URL + PM.Page_URL URL
from
Role_Page_Map RPM
,Module_Master MM
,Page_Master PM
where RPM.Role_ID = @.i_Role_ID
and RPM.Customer_Id = @.i_Customer_Id
and RPM.Page_ID = PM.Page_ID
and PM.Module_ID = MM.Module_ID
for XML Auto, Elements|||You can't select FOR XML into a table. See BOL for more information.|||That's true. However can't the resultant xml string be taken in a varchar variable as well?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment