SQL Server extracts the node data from the XML file into the database

Source: http://blog.csdn.net/kk185800961/article/details/12350715 Reprint Please specify the source

XML instance file:

2. <? XML version="1.0" encoding="Utf-8" ?>
4. <!--edited with XMLSpy v2010 (http://www.altova.com) by Fengshuai (founder)-   
6. < Root >   
8. < Frame >   
10. < Item >   
12. < ID > 0 </ ID >   
14. < Num > 1 </ Num >   
16. < rs_rate > (240,240) </ rs_rate >   
18. < Mode > AA </ Mode >   
20. < Rate > the </ Rate >   
22. < modulation > BPSK </ modulation >   
24. < Type > Design </ Type >   
26. </ Item >   
28. < Item >   
30. < ID > 1 </ ID >   
32. < Num > 2 </ Num >   
34. < rs_rate > (240,192) </ rs_rate >   
36. < Mode > KK </ Mode >   
38. < Rate > the </ Rate >   
40. < modulation > QPSK </ modulation >   
42. < Type > Personality </ Type >   
44. </ Item >   
46. </ Frame >   
48. < Config >   
50. < Service >   
52. < Service_item >   
54. < SID > 254 </ SID >   
56. < MID > 1 </ MID >   
58. < Mode > GG </ Mode >   
60. < Band > 27648 </ Band >   
62. < isscramble > false </ isscramble >   
64. </ Service_item >   
66. </ Service >   
68. </ Config >   
70. </ Root >   

--More references (openrowset,sp_xml_preparedocument)

SQL statements:

2. DECLARE @idoc int   
4. DECLARE @xml XML
6. SELECT @xml =bulkcolumn from OPENROWSET (BULK ' F:\360Downloads\Apk\test.xml ', Single_blob) as x
8. SELECT @xml
12. EXEC sp_xml_preparedocument @Idoc OUTPUT , @xml
18. SELECT  *  into  # Temp from OPENXML (@Idoc, '/root/frame/item ', 2)
20. with  (  
22. ID INT
24. , Num INT
26. , rs_rate varchar(TEN)
28. , Mode varchar(TEN)
30. , rate varchar(TEN)
32. , modulation varchar(TEN)
34. , Type varchar(TEN)
36. )
40. Select  *  from  # Temp   
44. Drop table # temp