SQLServer2005分解并导入xml文件

测试环境SQL2005，windows2003

DECLARE   @idoc   int ;
DECLARE   @doc  xml;

 
SELECT   @doc = bulkcolumn  FROM   OPENROWSET (
    BULK   ' D: /test.xml ' ,
   SINGLE_BLOB)  AS  x
 
EXEC  sp_xml_preparedocument  @Idoc  OUTPUT,  @doc
 
  SELECT   *   into  # temp   FROM  OPENXML ( @Idoc ,  ' /Root/Item ' , 2 )
    WITH  (
             [ ID ]   varchar ( 10 )
           , [ Name ] varchar ( 10 )
           , [ Caption ] varchar ( 10 )
         )
select   *   from  # temp
drop   table  # temp

/**/ /*--文件D: est.xml的文本内容
<?xml version="1.0" encoding="UTF-8"?>
<Root>
  <Item>
    <ID>1</ID>
    <Name>jinjazz</Name>
    <Caption>剪刀</Caption>
  </Item>
  <Item>
    <ID>2</ID>
    <Name>zswang</Name>
    <Caption>伴水</Caption>
  </Item>
</Root>
*/

/**/ /*---查询结果
ID         Name       Caption
---------- ---------- ----------
1          jinjazz    剪刀
2          zswang     伴水
*/