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的文本内容
<?xmlversion="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>
*/

/**/ /*---查询结果
IDNameCaption
------------------------------
1jinjazz剪刀
2zswang伴水
*/