if exists(select * from master..sysobjects where id=object_id('xmltb'))
drop table xmltb
if exists(select * from master..sysobjects where id=object_id('xml_tmp'))
drop table xml_tmp
go
use master
go
create table master..xmltb(xmlstr varchar(8000))
-- 创建一个零时数据表
BULK INSERT master.dbo.[xmltb]
--把你XML创建到 c盘下 MYXML.XML 文件
FROM 'c:\myxml.xml'
WITH ( ROWTERMINATOR = '\n' )
--或者 exec master..xp_cmdshell 'bcp master..xmltb in c:\myxml.xml -c'
exec ('alter table xmltb ADD I int identity(1,1)')godeclare @i int,@sql varchar(8000),@col varchar(8000),@value varchar(8000),@str varchar(8000)
select @i=0,@sql='',@col='',@value='',@str=''while @i!=-1
begin
select @i=@i+1,@col='',@value='',@str='',@sql=''
select @sql=xmlstr from xmltb where i=@i
select @sql=stuff(@sql,1,2,'')
if (substring(ltrim(@sql),1,4)='<LIB')
begin
while len(@sql)>0
begin
if not exists(select * from master..sysobjects where id=object_id('xml_tmp'))
begin
select @sql=stuff(@sql,1,charindex(' ',@sql),'')
select @col=@col+','+substring(@sql,1,(charindex('=',@sql)-1))+' varchar(100)'
end
select @sql=stuff(@sql,1,charindex('"',@sql),'')
select @value=@value+','''+substring(@sql,1,(charindex('"',@sql)-1))+''''
select @sql=stuff(@sql,1,charindex('"',@sql),'')
if replace(@sql,' ','')='/>'
begin
select @sql = 'create table xml_tmp('+stuff(@col,1,1,'')+')'
if not exists(select * from master..sysobjects where id=object_id('xml_tmp'))
exec(@sql)
select @sql = 'insert into xml_tmp select '+stuff(@value,1,1,'')+''
exec (@sql)
break
end
end
end
else
begin
if isnull(@sql,'')=''
break
continue
end
endselect * from xml_tmp
--察看结果
drop table xmltb
if exists(select * from master..sysobjects where id=object_id('xml_tmp'))
drop table xml_tmp
go
use master
go
create table master..xmltb(xmlstr varchar(8000))
-- 创建一个零时数据表
BULK INSERT master.dbo.[xmltb]
--把你XML创建到 c盘下 MYXML.XML 文件
FROM 'c:\myxml.xml'
WITH ( ROWTERMINATOR = '\n' )
--或者 exec master..xp_cmdshell 'bcp master..xmltb in c:\myxml.xml -c'
exec ('alter table xmltb ADD I int identity(1,1)')godeclare @i int,@sql varchar(8000),@col varchar(8000),@value varchar(8000),@str varchar(8000)
select @i=0,@sql='',@col='',@value='',@str=''while @i!=-1
begin
select @i=@i+1,@col='',@value='',@str='',@sql=''
select @sql=xmlstr from xmltb where i=@i
select @sql=stuff(@sql,1,2,'')
if (substring(ltrim(@sql),1,4)='<LIB')
begin
while len(@sql)>0
begin
if not exists(select * from master..sysobjects where id=object_id('xml_tmp'))
begin
select @sql=stuff(@sql,1,charindex(' ',@sql),'')
select @col=@col+','+substring(@sql,1,(charindex('=',@sql)-1))+' varchar(100)'
end
select @sql=stuff(@sql,1,charindex('"',@sql),'')
select @value=@value+','''+substring(@sql,1,(charindex('"',@sql)-1))+''''
select @sql=stuff(@sql,1,charindex('"',@sql),'')
if replace(@sql,' ','')='/>'
begin
select @sql = 'create table xml_tmp('+stuff(@col,1,1,'')+')'
if not exists(select * from master..sysobjects where id=object_id('xml_tmp'))
exec(@sql)
select @sql = 'insert into xml_tmp select '+stuff(@value,1,1,'')+''
exec (@sql)
break
end
end
end
else
begin
if isnull(@sql,'')=''
break
continue
end
endselect * from xml_tmp
--察看结果
解决方案 »
- sql中查询某天两个小时间段的数据!有没有通用的方法?
- datatime 转换 data
- 存贮过程执行后返回一个结果集,如何执行存贮过程后将结果导入到一张表中呢?[
- SOS
- 请问:在SQL,自定义函数能否调用存储过程?
- Windows 7系统 下安装SQL SERVER 2000 企业版 出现兼容问题,该怎么办?
- sql中怎樣把一執行語句的值賦給一個變量
- 紧急!如何用SQL2000的BCP工具倒入由SQL6.5BCP工具倒出的数据(倒出倒入时,没有使用format file)在线等。
- 大家帮忙,我想把我建好的表里的一列改为标志列,请问sql语句怎么写啊?
- 多字符串取交集的方法
- 机械类生产计划与BOM生成物料相关需求的存储过程(向大家请教如何修改)
- 诚心请教: Sql2005缓存依赖项 更新无效的问题 三天了 败给它了!
go
create table hzb..xmltb(xmlstr varchar(8000))
exec master..xp_cmdshell 'bcp hzb..xmltb in d:\myxml.xml -c '
exec ('alter table xmltb ADD I int identity(1,1)')select *
from xmltb怎么表中没有一条记录呢?
DECLARE @doc varchar(1000)
set @doc =
'
<LIB Storage="storage" ConnectCode="0002" CtrlDvsnCD="00027495" ProcessFlag="1" SequenceNo="20070225000000000001" CompanyCode="77946311" BranchCode="0400" BranchName="" ShopCode="0400" ShopGCode="77802992" ShopName="深圳总仓" ShopProvince="广东省" ShopCity="深圳" GoodsCode="100386" GoodsName="ES4823A/S 松下电动须刀" GoodsType="ES4823A/S" SupplyCode="6022" GoodsGCode="ES4823S405" ClassCode="D03" ClassName="其它白色小家电类" Qty="40" StatQty="41" UnitName="台" StorageType="N" StatTime="2007-02-24 00:00:00" CreatedTime="2007-02-25 01:27:59" SendTime="2007-02-25 01:27:59" TestID="0" />
<LIB Storage="storage" ConnectCode="0002" CtrlDvsnCD="00027495" ProcessFlag="1" SequenceNo="20070225000000000002" CompanyCode="77946311" BranchCode="0400" BranchName="" ShopCode="0400" ShopGCode="77802992" ShopName="深圳总仓" ShopProvince="广东省" ShopCity="深圳" GoodsCode="100387" GoodsName="ES5821S松下电动须刀" GoodsType="ES5821S" SupplyCode="6022" GoodsGCode="ES5821S405" ClassCode="D03" ClassName="其它白色小家电类" Qty="59" StatQty="60" UnitName="台" StorageType="N" StatTime="2007-02-24 00:00:00" CreatedTime="2007-02-25 01:27:59" SendTime="2007-02-25 01:27:59" TestID="0" />
.....
EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *
FROM OPENXML (@idoc, '/LIB',1)
WITH (Storage varchar(20),ConnectCode varchar(20)....)EXEC sp_xml_removedocument @idoc'
看看下面这个DECLARE @idoc int
DECLARE @doc varchar(1000)
set @doc =
'
<root>
<LIB Storage="storage1" ConnectCode="001" />
<LIB Storage="storage2" ConnectCode="0002" />
</root>
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *
FROM OPENXML (@idoc, '/root/LIB',0)
WITH (Storage varchar(20),ConnectCode varchar(20))EXEC sp_xml_removedocument @idoc