将文本追加到FOR XML SQL Select语句 [英] Appending Text to FOR XML SQL Select Statement
问题描述
来自SQL nO0b的问候,
我想加入三个Select
语句:
Hello from an SQL nO0b,
I have three Select
statements I want to join:
选择2是要添加到的FOR XML
选择Select 1& 3(仅显示一些文本)作为一种页眉/页脚.
Select 2 is a FOR XML
Select to which I want to append Selects 1 & 3 (which simply display some text) as a kind of header/footer.
所有三个选择均按预期工作,并形成格式正确的podcast .xml的三个部分,但是我需要它们以一个结果结束,以便最终将其保存到.xml文件中(这将是我的下一个麻烦事) , 我想).
All three Selects work as intended and form three parts of a nicely-formed podcast .xml, but I need them to end up in one result so I can eventually save it to an .xml file (which will be my next headache, I suppose).
我尝试了+
,UNION
和JOIN
的排列,这些经验不足的人可以提出,但无济于事.
I have tried permutations of +
, UNION
, and JOIN
that my inexperienced mind can come up with, but to no avail.
选择#1(仅输入一些文字)
Select '<?xml version=''1.0''?>
<rss xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0">
<channel>
<atom:link href="http://www.mysite.com/podcast/podcast.xml" rel="self" type="application/rss+xml" />
<itunes:image href="http://www.mysite.com/Portals/0/Images/Audio/podcast_logo.jpg"/>
<link>http://www.mysite.com/Audio.aspx</link>
<title>My Podcast</title>
<description>My Podcast's Description</description>
<language>en-us</language>
<copyright>© 2010</copyright>
<itunes:subtitle>Subtitle</itunes:subtitle>
<itunes:owner>
<itunes:name>Name</itunes:name>
<itunes:email>user@domain.com</itunes:email>
</itunes:owner>
<itunes:category text="Religion & Spirituality">
<itunes:category text="Christianity" />
</itunes:category>
<itunes:explicit>No</itunes:explicit>
<ttl> 120 </ttl>'
选择#2(我的数据)
Select
(Select Cast(FieldValue as nvarchar(max)) from dbo.UserDefinedData where UserDefinedFieldId = 305 and UserDefinedRowId = item.UserDefinedRowId) as [title]
,(Select 'Today''s Program is ' + Cast(FieldValue as nvarchar(max)) from dbo.UserDefinedData where UserDefinedFieldId = 305 and UserDefinedRowId = item.UserDefinedRowId) as [description]
,(Select Cast(Cast(FieldValue as nvarchar(max)) as DateTime) from dbo.UserDefinedData where UserDefinedFieldId = 306 and UserDefinedRowId = item.UserDefinedRowId) as [pubdate]
,(Select 'http://www.mysite.com/Portals/0/AudioFiles/RadioArchives/' + Cast(FieldValue as nvarchar(max)) from dbo.UserDefinedData where UserDefinedFieldId = 308 and UserDefinedRowId = item.UserDefinedRowId) as [guid]
From
dbo.UserDefinedRows item
Where
ModuleId = 820
and UserDefinedRowID in (select UserDefinedRowID from UserDefinedData where UserDefinedFieldID = 306 and Cast(Cast(FieldValue as nvarchar(max)) as DateTime) between '2010-Nov-11' and '2010-Nov-18')
Order By
[pubdate] DESC
for XML AUTO,ELEMENTS
选择#3(更多文字)
Select '</channel>
</rss>'
(我正在使用MS SQLSMS2008.) (第二个选择语句的复杂性是由于它要从必须首先展平"的DotNetNuke用户定义的表/表单和列表"中检索数据这一事实.)
(I am using MS SQLSMS 2008.) (The complex nature of the second select statement is due to the fact that it is retrieving data from a DotNetNuke "User-Defined Table/Form & List" which must first be "flattened".)
谢谢您的任何帮助.
推荐答案
Declare @V1 VarChar(Max), @V2 VarChar(Max), @V3 VarChar(Max)
Select @V1 = ...
Set @V2 = (Select ...)
Select @V3 = ...
Select @V1+@V2+@V3
或者,将#1和#3转换为XML.
Alternatively, cast #1 and #3 to XML.
这篇关于将文本追加到FOR XML SQL Select语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!