SQLServer2005·Ö½â²¢µ¼ÈëxmlÎļþ

  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     °éË®
*/