Sql Server存储过程传递XML参数

Create tables for later population using OPENXML. 
CREATE TABLE Customers (CustomerID varchar(20) primary key, 
                 ContactName varchar(20),  
                 CompanyName varchar(20)); 
GO 
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO 
DECLARE @docHandle int; 
DECLARE @xmlDocument nvarchar(max); -- or xml type 
SET @xmlDocument = N'<ROOT> 
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1"> 
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/> 
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/> 
</Customers> 
<Customers CustomerID="XYZBB" ContactName="Steve" 
CompanyName="Company2">No Orders yet! 
</Customers> 
</ROOT>'; 
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument; 
-- Use OPENXML to provide rowset consisting of customer data. 
INSERT Customers  
SELECT *  
FROM OPENXML(@docHandle, N'/ROOT/Customers')  
   WITH Customers; 
-- Use OPENXML to provide rowset consisting of order data. 
INSERT Orders  
SELECT *  
FROM OPENXML(@docHandle, N'//Orders')  
   WITH Orders; 
-- Using OPENXML in a SELECT statement. 
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders')
   WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document. 
EXEC sp_xml_removedocument @docHandle;
 

image

DECLARE @idoc int, @doc xml 
SET @doc =' 
<ROOT> 
<Customer CustomerID="VINET" ContactName="Paul Henriot"> 
    <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> 
       <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> 
       <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> 
    </Order> 
</Customer> 
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> 
    <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> 
       <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> 
    </Order> 
</Customer> 
</ROOT>'; 
--Create an internal representation of the XML document. 
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; 
-- Execute a SELECT statement that uses the OPENXML rowset provider. 
SELECT    * 
FROM       OPENXML (@idoc, '/ROOT/Customer',1) 
             WITH (CustomerID  varchar(10), 
                   ContactName varchar(20)); 
EXEC sp_xml_removedocument @idoc

image

/ROOT/Customer/Order

image

/ROOT/Customer/Order/OrderDetail

image

 

posted @ 2020-05-23 11:06  李华丽  阅读(687)  评论(0编辑  收藏  举报
AmazingCounters.com