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

2023-12-11 23:54:46

原文出处：http://blog.csdn.net/kk185800961/article/details/12350715 转载请注明出处

XML实例文件：

<?xml version="1.0" encoding="utf-8"?>
<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>

SQL语句：

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

码农公寓