本实例通过Sqlserver读取XML文件,实现导入功能;文件以年月日命名,如:20230821.xml
CREATE proc [dbo].[pr_每天导入]
as
declare @s varchar(1000)
declare @sql varchar(1000)
set @s=CONVERT(char(8),GETDATE(),112)+'.xml'
select @s
set @sql=
'
if object_id(''每天更新'') is not null drop table 每天更新
SELECT *
into 每天更新
FROM OPENROWSET(BULK ''E:\ftp\luoen\Sigma\'+@s+''',SINGLE_BLOB) AS x'
exec(@sql)
DECLARE @hdoc int
declare @Asqlstring xml
select @Asqlstring=BulkColumn from 每天更新
select
brand=T.c.value('(brand/text())[1]','nVARCHAR(100)'),
goodsNo=T.c.value('(goodsNo/text())[1]','nVARCHAR(50)'),
spec=T.c.value('(spec/text())[1]','nVARCHAR(50)'),
cas=T.c.value('(cas/text())[1]','nVARCHAR(50)'),
cnName=T.c.value('(cnName/text())[1]','nvarchar(500)'),
enName=T.c.value('(enName/text())[1]','nVARCHAR(500)'),
imageUrl=T.c.value('(imageUrl/text())[1]','nVARCHAR(1000)'),
purity=T.c.value('(purity/text())[1]','nVARCHAR(50)'),
listPrice=T.c.value('(listPrice/text())[1]','nVARCHAR(50)'),
ifHidden=T.c.value('(ifHidden/text())[1]','nVARCHAR(50)'),
storageCond=T.c.value('(storageCond/text())[1]','nVARCHAR(500)'),
storageTemperature=T.c.value('(storageTemperature/text())[1]','nVARCHAR(50)'),
transportCond=T.c.value('(transportCond/text())[1]','nVARCHAR(500)'),
molFormat=T.c.value('(molFormat/text())[1]','nVARCHAR(50)'),
molWeight=T.c.value('(molWeight/text())[1]','nVARCHAR(50)'),
MDL=T.c.value('(MDL/text())[1]','nVARCHAR(50)'),
productFstType=T.c.value('(productFstType/text())[1]','nVARCHAR(200)'),
productSecondType=T.c.value('(productSecondType/text())[1]','nVARCHAR(200)'),
productThirdType=T.c.value('(productThirdType/text())[1]','nVARCHAR(200)'),
stockNumBj=T.c.value('(stockNumBj/text())[1]','nVARCHAR(30)'),
stockNumSh=T.c.value('(stockNumSh/text())[1]','nVARCHAR(30)'),
stockNumCd=T.c.value('(stockNumCd/text())[1]','nVARCHAR(30)'),
stockNumGz=T.c.value('(stockNumGz/text())[1]','nVARCHAR(30)'),
stockNumInternal=T.c.value('(stockNumInternal/text())[1]','nVARCHAR(30)'),
meltPoint=T.c.value('(meltPoint/text())[1]','nVARCHAR(50)'),
boilPoint=T.c.value('(boilPoint/text())[1]','nVARCHAR(50)'),
[description]=T.c.value('(description/text())[1]','VARCHAR(5000)'),
remark=T.c.value('(remark/text())[1]','VARCHAR(5000)')
into #b
FROM @Asqlstring.nodes('/Products/Product') AS T(c)
INSERT INTO [dbo].[实际表_每日更新]
([brand],[goodsNo],[spec],[cas],[cnName],[enName],[imageUrl],[purity]
,[listPrice],[ifHidden],[storageCond],[storageTemperature],[transportCond],[molFormat]
,[molWeight],[MDL],[productFstType],[productSecondType],[productThirdType]
,[stockNumBj],[stockNumSh],[stockNumCd],[stockNumGz],[stockNumInternal],[meltPoint],[boilPoint],[description])
select [brand],[goodsNo],[spec],[cas],[cnName],[enName],[imageUrl],[purity],[listPrice],[ifHidden]
,[storageCond],[storageTemperature],[transportCond],[molFormat]
,[molWeight],[MDL],[productFstType],[productSecondType],[productThirdType]
,[stockNumBj],[stockNumSh],[stockNumCd],[stockNumGz],[stockNumInternal],[meltPoint],[boilPoint],isnull(remark,'')+'。'+isnull([description],'')
from #b
if object_id('每天更新') is not null drop table 每天更新
drop table #b
insert 接口访问情况(日期,品牌,条数,时间)
select
CONVERT(char(8),GETDATE(),112),
'xxx',
(select COUNT(1) from dbo.实际表_每日更新 where 日期=CONVERT(char(8),GETDATE(),112)),
getdate()
XML格式:
<Products>
<Product>
<brand>Supelco</brand>
<goodsNo>00020590-100MG</goodsNo>
<spec>100mg</spec>
<cas>470-82-6</cas>
<cnName>1,8-桉叶素</cnName>
<enName>1,8-Cineole</enName>
<imageUrl></imageUrl>
<purity></purity>
<listPrice>6186.84</listPrice>
<ifHidden>1</ifHidden>
<storageCond></storageCond>
<storageTemperature>−20°C</storageTemperature>
<transportCond></transportCond>
<molFormat></molFormat>
<molWeight>154.25</molWeight>
<MDL>MFCD00167977</MDL>
<productFstType></productFstType>
<productSecondType></productSecondType>
<productThirdType></productThirdType>
<stockNumBj>3</stockNumBj>
<stockNumSh>0</stockNumSh>
<stockNumCd>0</stockNumCd>
<stockNumGz>0</stockNumGz>
<stockNumInternal>3</stockNumInternal>
<remark>常规RED产品</remark>
<meltPoint>1-2 °C (lit.)</meltPoint>
<boilPoint>176-177 °C (lit.)</boilPoint>
<description>1,8-桉叶素是精油的主要成分。它是一种萜烯氧化物。它可用作经皮渗透促进剂、皮肤浴中的皮肤兴奋剂,并用于治疗支气管炎、鼻窦炎和风湿病。它具有解充血和镇咳作用。</description>
</Product>
</Products>