首页 > 数据库 >SQLServer 从xml 文件中提取节点数据到数据库中

SQLServer 从xml 文件中提取节点数据到数据库中

时间:2023-01-10 12:02:06浏览次数:64  
标签:xml 10 varchar OPENROWSET SQLServer Idoc 240 节点



<?xml version="1.0" encoding="utf-8"?>
<!-- edited with XMLSpy v2010 (http://www.altova.com) by fengshuai (founder) -->
<Root>
<Frame>
<Item>
<ID>0</ID>
<Num>1</Num>
<RS_Rate>(240,240)</RS_Rate>
<Mode>AA</Mode>
<Rate>1/2</Rate>
<Modulation>BPSK</Modulation>
<Type>设计</Type>
</Item>
<Item>
<ID>1</ID>
<Num>2</Num>
<RS_Rate>(240,192)</RS_Rate>
<Mode>kk</Mode>
<Rate>1/2</Rate>
<Modulation>QPSK</Modulation>
<Type>人格</Type>
</Item>
</Frame>
<Config>
<Service>
<Service_Item>
<SID>254</SID>
<MID>1</MID>
<Mode>GG</Mode>
<Band>27648</Band>
<IsScramble>false</IsScramble>
</Service_Item>
</Service>
</Config>
</Root>



--更多参考(​​OPENROWSET​​​,​​sp_xml_preparedocument​​ )

/*注意:字段的大小写须与xml中对应一致!*/

DECLARE @idoc int
DECLARE @xml xml
SELECT @xml=bulkcolumn FROM OPENROWSET( BULK 'F:\360Downloads\Apk\test.xml', SINGLE_BLOB) AS x
SELECT @xml

EXEC sp_xml_preparedocument @Idoc OUTPUT, @xml


SELECT * into #temp FROM OPENXML (@Idoc, '/Root/Frame/Item',2)
WITH (
ID INT
,Num INT
,RS_Rate varchar(10)
,Mode varchar(10)
,Rate varchar(10)
,Modulation varchar(10)
,Type varchar(10)
)

select * from #temp

drop table #temp



SQLServer 从xml 文件中提取节点数据到数据库中_xml













标签:xml,10,varchar,OPENROWSET,SQLServer,Idoc,240,节点
From: https://blog.51cto.com/hzc2012/6000182

相关文章