当前位置: 代码迷 >> Sql Server >> 怎么用存储过程将一张表的内容按层次放入xml表中
  详细解决方案

怎么用存储过程将一张表的内容按层次放入xml表中

热度:143   发布时间:2016-04-27 19:35:55.0
如何用存储过程将一张表的内容按层次放入xml表中
有一个表a,他里面有数据(Previous_Flight,Next_Flight,Enter_Corridor_Time,   Recent_Abnormal_Status   ,Reported_Abnormal,Delay_Code,   Cause_Of_Delay等有100多个)有200行的样子,现在要从a表里取出所有数据,利用存储过程,做成XML格式。例如
Previous_Flight   AC133
Next_Flight   CA355
Enter_Corridor_Time   2007-09-21   19:00:00
Recent_Abnormal_Status   1
Reported_Abnormal   2
Delay_Code   0
Cause_Of_Delay   rain
<Dailys>
<daily_values   instance= "Previous_Flight "> AC133 <daily_values>
                  <daily_values   instance= "Next_Flight "> CA355 <daily_values>
                  <daily_values   instance= "Enter_Corridor_Time "> 2007-09-21   19:00:00 <daily_values>
                  <daily_values   instance= "Recent_Abnormal_Status "> 1 <daily_values>
                  <daily_values   instance= "Reported_Abnormal "> 2 <daily_values>
                  <daily_values   instance= "Delay_Code "> 0 <daily_values>
                  <daily_values   instance= "Cause_Of_Delay "> rain <daily_values>
                  ......
</Dailys>
请教一下,它的存储过程应该怎么样

------解决方案--------------------
准备数据
Create table Tb_ Customer (id int,name nvarchar(10),Sex nvarchar(10),Age int)
insert into tb_Customer (Name,Sex,Age)(
 select '张三' as name,'男' as Sex,20 as Age union all
 select '李三' as name,'男' as Sex,20 as Age union all
 select '王三' as name,'男' as Sex,20 as Age union all
 select '赵三' as name,'女' as Sex,19 as Age 
)
解决方案
declare @DataXml XML
declare @XMLString varchar(4000)
set @DataXml=(select id as "daily_values instance='ID'" ,
name as "daily_values instance='name'" ,
Sex as "daily_values instance='Sex'" ,
Age as "daily_values instance='Age'" 
from tb_customer
for xml auto,ELEMENTS,ROOT('Dailys'))
set @XMLString =convert(varchar(4000),@DataXml)
set @XMLString=replace(@XMLString,'_x0020_',' ')
set @XMLString=replace(@XMLString,'_x003D_','=')
set @XMLString=replace(@XMLString,'_x0027_','"')
print @XMLString




  相关解决方案