34,594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<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>
</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>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
--Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order',1)
WITH (OrderID varchar(10),
CustomerID varchar(20),
EmployeeID varchar(20),
OrderDate varchar(20))
EXEC sp_xml_removedocument @idoc
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<output> <akc190>11012720080911000032</akc190><yka180>0</yka180><yka270>280</yka270></output>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/output',1)
WITH (akc190 varchar(100),
yka180 varchar(20),
yka270 varchar(20))
EXEC sp_xml_removedocument @idoc
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<output>
<Col akc190 = "11012720080911000032" yka180 = "0" yka270 = "280">
</Col>
</output>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/output/Col',1)
WITH (akc190 varchar(100),
yka180 varchar(20),
yka270 varchar(20))
EXEC sp_xml_removedocument @idoc
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<output> <akc190>11012720080911000032</akc190><yka180>0</yka180><yka270>280</yka270></output>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/output',2)
WITH (akc190 varchar(100),
yka180 varchar(20),
yka270 varchar(20))
EXEC sp_xml_removedocument @idoc
--结果
akc190 yka180 yka270
---------------------------------------------------------------------------------------------------- -------------------- --------------------
11012720080911000032 0 280
(1 行受影响)
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<output><akc190>11012720080911000032</akc190><yka180>0</yka180><yka270>280</yka270></output>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/output',2)
WITH (akc190 varchar(100),
yka180 varchar(20),
yka270 varchar(20))
EXEC sp_xml_removedocument @idoc
/*
akc190 yka180 yka270
------------------------------------
11012720080911000032 0 280
*/
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<output>
<akc190>11012720080911000032</akc190>
<yka180>0</yka180>
<yka270>280</yka270>
</output>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/output',2) --使用2参数,匹配元素.1是匹配属性..
WITH (akc190 varchar(100),
yka180 varchar(20),
yka270 varchar(20))
EXEC sp_xml_removedocument @idoc
/*
akc190 yka180 yka270
---------------------------------------------------------------------------------------------------- -------------------- --------------------
11012720080911000032 0 280
(1 行受影响)
*/
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<output> <akc190>11012720080911000032</akc190><yka180>0</yka180><yka270>280</yka270></output>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/output',2) -- 改成2
WITH (akc190 varchar(100),
yka180 varchar(20),
yka270 varchar(20))
EXEC sp_xml_removedocument @idoc
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<output>
<akc190>11012720080911000032</akc190>
<yka180>0</yka180>
<yka270>280</yka270>
</output>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/output',1)
WITH (akc190 varchar(100) 'akc190',
yka180 varchar(20) 'yka180',
yka270 varchar(20) 'yka270')
EXEC sp_xml_removedocument @idoc
/*
akc190 yka180 yka270
---------------------------------------------------------------------------------------------------- -------------------- --------------------
11012720080911000032 0 280
(1 行受影响)
*/