T-Sql将XML列数据作为FOR XML查询中的节点返回

I have a view in SQL Server 2005 that contains a column of xml data. The column comes from a for xml path() query, ie

我在SQL Server 2005中有一个包含一列xml数据的视图。该列来自for xml path（）查询，即

SELECT     e.id, eventTypeCode, e.startDate, e.endDate,
(select v.name 
 from venue v inner 
 join eventVenue ev on ev.venueCode=v.code 
 where ev.eventId=e.id for xml path('venue')) as venues    
FROM dbo.event e 
inner join eventType t on e.eventTypeCode=t.code

I now want to refer to this view in a stored procedure that returns data as xml, ie

我现在想在存储过程中引用此视图，该数据以xml形式返回数据，即

create procedure getWebsiteMainCalendarEvents
@startDate datetime,
@endDate datetime
as
select * from vwWebsiteMainCalendar 
 where startDate between @startDate and @endDate 
order by startDate for xml path() element

I'd like the venues from the view to appear as subnodes when I run the procedure, but < and > are encoded as html entities. Is there a way to get the procedure to treat xml data as an xml node in the output?

当我运行程序时，我希望视图中的场所显示为子节点，但 <和> 编码为html实体。有没有办法让xml数据作为输出中的xml节点处理？

1 个解决方案

#1

I've sorted it. The view should have had 'type' attached to the for xml path(), ie

我把它分类了。视图应该有'type'附加到for xml path（），即

-- VENUES
(select v.name from venue v inner join eventVenue ev on ev.venueCode=v.code where ev.eventId=e.id for xml path('venue'))

should be

应该

-- VENUES

(select v.name from venue v inner join eventVenue ev on ev.venueCode=v.code where ev.eventId=e.id for xml path('venue'), type)

（从ev.venueCode = v.code中选择场地v内连接eventVenue ev的v.name，其中ev.eventId = e.id用于xml路径（'场地'），输入）

#1