SqlServer每天读取XML文件

 本实例通过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>