sql server 解读xml

DECLARE @idoc int
DECLARE @doc varchar(1000)
set @doc=N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
   <test>12</test>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
   <test>333</test>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')


SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',1)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProductID  int   ,     
               Quantity  int     )

SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',2)
            WITH (
				  test varchar(20))


EXEC sp_xml_removedocument @idoc

注：openxml会造成性能损失，建议用自带的函数。

 

 

　openxml：第三个参数：1读属性，2读元素（注：可用@符号进行转换）