数据库中的xml类型字段的问题
1
悬赏园豆：10
[已关闭问题]
关闭于 2011-07-01 11:40
View Code
CREATE TABLE Orders( CustomerID varchar(20), OrderDate varchar(25))
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max);
SET @xmlDocument = N'<ROOT>
<Customers>
<Orders>
<CustomerID>XYZAA</CustomerID>
<OrderDate>2000-08-25T00:00:00</OrderDate>
</Orders>
<Orders>
<CustomerID>XYZAA</CustomerID>
<OrderDate>2000-10-03T00:00:00</OrderDate>
</Orders>
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Orders
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/orders')
WITH (CustomerID nchar(5) 'CustomerID',
OrderDate nchar(25) 'OrderDate')
select * from Orders
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle
drop table Orders
View Code 我就是想将customid和orderdate中的数据取出来，上面的代码是取不出来的，怎么写呀？请高手指教
CREATE TABLE Orders( CustomerID varchar(20), OrderDate varchar(25))
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max);
SET @xmlDocument = N'<ROOT>
<Customers>
<Orders>
<CustomerID>XYZAA</CustomerID>
<OrderDate>2000-08-25T00:00:00</OrderDate>
</Orders>
<Orders>
<CustomerID>XYZAA</CustomerID>
<OrderDate>2000-10-03T00:00:00</OrderDate>
</Orders>
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Orders
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/orders')
WITH (CustomerID nchar(5) 'CustomerID',
OrderDate nchar(25) 'OrderDate')
select * from Orders
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle
drop table Orders
