Source: http://blog.csdn.net/kk185800961/article/details/12350715 Reprint Please specify the source
XML instance file:
- <? 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 > the </ Rate >
- < modulation > BPSK </ modulation >
- < Type > Design </ Type >
- </ Item >
- < Item >
- < ID > 1 </ ID >
- < Num > 2 </ Num >
- < rs_rate > (240,192) </ rs_rate >
- < Mode > KK </ Mode >
- < Rate > the </ Rate >
- < modulation > QPSK </ modulation >
- < Type > Personality </ 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 >
--More references (openrowset,sp_xml_preparedocument)
SQL statements:
- 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(TEN)
- , Mode varchar(TEN)
- , rate varchar(TEN)
- , modulation varchar(TEN)
- , Type varchar(TEN)
- )
- Select * from # Temp
- Drop table # temp