怎么不用临时表实现需要的这样的功能_Sql Server

如何不用临时表实现需要的这样的功能

本帖最后由 u010349289 于 2014-03-16 15:00:14 编辑

有一个数据表，大概是这样：

[ContentIN]字段里保存的是xml数据，实际上也是一个数据表形成的xml字符串。

要实现以下功能：
1、根据[status]字段的值，比如获得[status]字段等于‘00’的所有记录
2、使用OPENXML将每条记录里的[ContentIN]字段中xml的数据转成table（OPENXML使用没有问题）
3、将每条记录[ContentIN]字段转成的table合并在一起返回

要求：全程不许使用#临时表（使用#临时表我自己已经实现），with as的临时表可以使用。
------解决方案--------------------
写成表函数不就行了？？
select * from table a
cross apply dbo.fn_XXXXXX(ContentIN) b
where [status] = '00'

--
函数 dbo.fn_XXXXXX(ContentIN) 返回表结果
------解决方案--------------------

引用:

下面就是我的代码，TD_Message 就是数据表：

ALTER PROCEDURE [dbo].[SP_GetMessageBatch]
AS
BEGIN
SET NOCOUNT ON;

--获得需要批量传送的包含xml原始数据的数据表
SELECT  ID, ContentIN into #tempTableBatchXml FROM TD_Message WITH(nolock)
where [Status]='00'
order by id

if exists(select id from #tempTableBatchXml)
begin
--声明一个内部保存xml文档的句柄
DECLARE @handler INT

declare @first int = 0
declare @ID int
declare @ContentIN nvarchar(max)
declare cur cursor for select ID,ContentIN from #tempTableBatchXml
open cur
--读取值，同时光标下移一行
fetch next from cur into @ID,@ContentIN
while @@fetch_status = 0
begin

EXEC sp_xml_preparedocument @handler OUTPUT, @ContentIN, ''
SELECT  @ID as id,* into #tempTableOneInvoice
FROM    OPENXML(@handler,'',1)
WITH(
发票行号 bigint '发票行号',  --返回的字段名，类型，xpath
发票代码号 nvarchar(50) '发票代码号',
发票号 nvarchar(50) '发票号'
)

--删除句柄，释放内存
EXEC sp_xml_removedocument @handler

if @first = 0
select * into #tempTableMerge from #tempTableOneInvoice
else
insert into #tempTableMerge select * from #tempTableOneInvoice

set @first = @first + 1

drop table #tempTableOneInvoice

--再次读取一次，以使光标下移一行
fetch next from cur into @ID,@ContentIN
end
--关闭光标
close cur
--释放光标
deallocate cur

select * from #tempTableMerge order by id,发票行号
drop table #tempTableMerge

end
drop table #tempTableBatchXml

END

哦，明白了，通过游标，一行一行的处理xml，解析成记录。

------解决方案--------------------

引用:

Quote: 引用:

写成表函数不就行了？？
select * from table a
cross apply dbo.fn_XXXXXX(ContentIN) b
where [status] = '00'

--
函数 dbo.fn_XXXXXX(ContentIN) 返回表结果

这就需要在表值函数中使用OPENXML解析xml表内容。
OPENXML要调用到sp_xml_preparedocument和sp_xml_removedocument 系统存储过程，而在表值函数中是不能调用存储过程的，怎么解？

SQL2005 以上版本可以直接申明xml 类型的变量，
该变量SQL提供了 value query nodes modify 等方法

比如 declare @x xml = '<Root a="123"> </Root>'
select @x.value('/Root[1]/@a','varchar(8)')

详细请看MSDN帮助

------解决方案--------------------
因为不清楚ContentIN字段xml的格式. 举如下例子,供参考.


create table TD_Message
(ID int,[Status] varchar(10),ContentIN xml)

insert into TD_Message(ID,[Status],ContentIN)
 select 1,'00',N'<Order><Item ItemNumber="0001"/><Item ItemNumber="0005"/></Order>' union all
 select 2,'00',N'<Order><Item ItemNumber="0035"/><Item ItemNumber="0040"/></Order>' union all
 select 3,'90',N'<Order><Item ItemNumber="9013"/><Item ItemNumber="9032"/></Order>' union all
 select 4,'90',N'<Order><Item ItemNumber="9027"/><Item ItemNumber="9028"/></Order>' union all
 select 5,'100',N'<Order><Item ItemNumber="0666"/><Item ItemNumber="0777"/></Order>' union all
 select 6,'100',N'<Order><Item ItemNumber="0888"/><Item ItemNumber="0999"/></Order>'


select a.ID,o.value('@ItemNumber','char(4)') 'ItemNumber'
 from
 (select ID,ContentIN
   from TD_Message 
   where [Status]='00') a
 cross apply ContentIN.nodes('/Order/Item') x(o)

/*
ID          ItemNumber
----------- ----------
1           0001
1           0005