测试环境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 #tempdrop 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 伴水*/