--联机帮助:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/689297f3-adb0-4d8d-bf62-cfda26210164.htm --eg1. DECLARE @idoc int; DECLARE @doc varchar(4000); SET @doc =' 2007-11-22 09:27:04 MOD A 21 E145 ' EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc --形成各节点(元素、属性、文本和注释等)的树状表示形式 返回一个句柄 SELECT * FROM OPENXML (@Idoc, '/FLIGHT/FLIGHTINFOR',2)WITH ( dt DATETIME '../DATETIME' ,METHOD varchar(20) '../METHOD' ,tp char(1)'../TYPE' ,FLIGHTATTR int 'FLIGHTATTR' ,AIRCRAFTTYPE varchar(10)'AIRCRAFTTYPE' ) exec sp_xml_removedocument @Idoc --结果: /* dt METHOD tp FLIGHTATTR AIRCRAFTTYPE 2007-11-22 09:27:04.000 MOD A 21 E145 */ --eg2. DECLARE @idoc int DECLARE @doc varchar(1000) SET @doc =' ' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- SELECT stmt using OPENXML rowset provider SELECT * FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2) WITH (CustomerID2 varchar(10) '../../@CustomerID' , ContactName varchar(20) '../../@ContactName', OrderID int '../@OrderID', CustomerID varchar(10) '../@CustomerID', OrderDate datetime '../@OrderDate', ProdID int '@ProductID', Qty int '@Quantity') --结果: /* CustomerID2 ContactName OrderID CustomerID OrderDate ProdID Qty VINET Paul Henriot 10248 VINET 1996-07-04 00:00:00.000 11 12 VINET Paul Henriot 10248 VINET 1996-07-04 00:00:00.000 42 10 LILAS Carlos Gonzlez 10283 LILAS 1996-08-16 00:00:00.000 72 3 */ --eg3. DECLARE @idoc int; DECLARE @doc varchar(4000); SET @doc =' sdfsdf 2007-11-22 09:27:04 MOD MOD23 MOD22 MOD2 5 A 21 E145 MOD22 dfsdfs ' EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc --形成各节点(元素、属性、文本和注释等)的树状表示形式 返回一个句柄 SELECT * FROM OPENXML (@Idoc, '/FLIGHT',2)WITH ( -- DATETIME2 DATETIME '../DATETIME' METHOD varchar(200) '..//METHOD/' -- ,TYPE char(1) '../TYPE' -- ,FLIGHTATTR int 'FLIGHTATTR' -- ,AIRCRAFTTYPE varchar(10)'AIRCRAFTTYPE' --,METHOD2 varchar(10) 'METHOD' ) exec sp_xml_removedocument @Idoc --结果: /* sdfsdf 2007-11-22 09:27:04 MOD MOD23 MOD22 MOD2 5 A 21 E145 MOD22 dfsdfs /*