sql server 解读xml

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

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读元素（注：可用@符号进行转换）