hoe将表转换为xml [英] hoe to convert the table as xml
本文介绍了hoe将表转换为xml的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
i输出应该是以下格式
i有eventid,日期,时间,持续时间,名称,概要作为我的数据库中的列
i试图隐藏在sql server中2005
i使用两个coloums进行测试
select(选择强制转换(service_ID as nvarchar)
+ cast(年份(转换(datetime,date,103))as nvarchar)
+子串(日期,Charindex('/',日期)+1,2)
+子串(日期,Charindex('/',日期)-2,2)
- + cast(datepart(dd,Convert(datetime,date,103))as nvarchar)
+'E'
+ cast((DENSE_RANK()OVER(PARTITION BY date ORDER BY frmtime ASC) )as nvarchar)as EVENTID
来自PRG_detail
其中service_ID = 101 FOR XML PATH(''),TYPE),
(SELECT A.DATE,A.frmtime as TIME FROM prg_detail FOR XML PATH('START'),TYPE)
FOR XML PATH(''),ROOT('EVENTSECTION')
但是输出首先是事件id和这样的开始部门
< EVENTSECTION >
< EVENTID > 10120140206E1 < / EVENTID >
< EVENTID > 10120140206E2 < / EVENTID >
< EVENTID > 10120140206E3 < / EVENTID >
< EVENTID > 10120140206E4 < / EVENTID >
< EVENTID > 10120140206E5 < / EVENTID >
< EVENTID > 10120140206E6 < / EVENTID >
< EVENTID > 10120140206E7 < / EVENTID >
< EVENTID > 10120140206E8 < / EVENTID >
< EVENTID > 10120140206E9 < / EVENTID >
< EVENTID > 10120140206E10 < / EVENTID >
< START >
< span class =code-keyword>< DA TE > 06/02/2014 < / DATE >
< TIME > 00:00:00 < / TIME >
< / START >
< START >
< DATE > 06/02/2014 < / DATE >
< ; TIME > 01:30:00 < / TIME >
< / START >
如何纠正这个
输出到
折叠|复制代码
< EVENT_SECTION >
< EVENTID > 47920140220E1 < / EVENTID >
< START >
< DATE > 2014/02/20 < / DATE >
< TIME > 00:00:00 < / TIME >
< / START >
< EPG_SECTION >
< DURATION > 00:30:00 < ; / DURATION >
< EPG 语言 = Eng >
< NAME > Comedy Express < / NAME >
< 大纲 < span class =code-keyword>> 该节目汇集了热门电影中的漫画场景。< / SYNOPSIS >
< LOG_LINE > Comedy Express < / LOG_LINE >
< / EPG >
< 主题 > 1 < / THEME >
< 评分 国家/地区 = IND > 0 < / RATING >
< / EPG_SECTION >
< / EVENT_SECTION >
解决方案
您好,您可以使用数据适配器获取数据到数据集(没有FOR XML PATH)
直接你可以使用数据集函数
dsMyData.WriteXml( D:\1.xml);
您好,
请添加
FOR XML AUTO
在查询结束时。
我希望这会对你有帮助。
i output should be in the below format
i have eventid, date, time, duration, name ,synopsis as columns in my database
i tried to covert in sql server 2005
i used two coloums for test
select (Select cast(service_ID as nvarchar)
+cast(Year(Convert(datetime,date,103))as nvarchar)
+Substring(date,Charindex('/',date)+1,2)
+Substring(date,Charindex('/',date)-2,2)
-- +cast(datepart(dd,Convert(datetime,date,103))as nvarchar)
+'E'
+cast((DENSE_RANK() OVER (PARTITION BY date ORDER BY frmtime ASC))as nvarchar) as EVENTID
from PRG_detail
where service_ID=101 FOR XML PATH(''), TYPE),
(SELECT A.DATE,A.frmtime as TIME FROM prg_detail A FOR XML PATH('START'), TYPE)
FOR XML PATH(''), ROOT('EVENTSECTION')
but output is event id first and the start division like this
<EVENTSECTION>
<EVENTID>10120140206E1</EVENTID>
<EVENTID>10120140206E2</EVENTID>
<EVENTID>10120140206E3</EVENTID>
<EVENTID>10120140206E4</EVENTID>
<EVENTID>10120140206E5</EVENTID>
<EVENTID>10120140206E6</EVENTID>
<EVENTID>10120140206E7</EVENTID>
<EVENTID>10120140206E8</EVENTID>
<EVENTID>10120140206E9</EVENTID>
<EVENTID>10120140206E10</EVENTID>
<START>
<DATE>06/02/2014</DATE>
<TIME>00:00:00</TIME>
</START>
<START>
<DATE>06/02/2014</DATE>
<TIME>01:30:00</TIME>
</START>
how to rectify this
OUTPUT TO COME
Collapse | Copy Code
<EVENT_SECTION>
<EVENTID>47920140220E1</EVENTID>
<START>
<DATE>2014/02/20</DATE>
<TIME>00:00:00</TIME>
</START>
<EPG_SECTION>
<DURATION>00:30:00</DURATION>
<EPG Language="Eng">
<NAME>Comedy Express</NAME>
<SYNOPSIS>The program features a compilation of comic scenes from popular films.</SYNOPSIS>
<LOG_LINE>Comedy Express</LOG_LINE>
</EPG>
<THEME>1</THEME>
<RATING Country="IND">0</RATING>
</EPG_SECTION>
</EVENT_SECTION>
解决方案
Hi , you can fetch data using data adapter to dataset(without FOR XML PATH)
directly you can use dataset function
dsMyData.WriteXml("D:\1.xml");
Hi,
Please add
FOR XML AUTO
in the end of the query.
I hope this will help you.
这篇关于hoe将表转换为xml的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文