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