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

<?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