CREATE TABLE Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20));
GO
CREATE TABLE Orders(CustomerID varchar(20), OrderDate datetime);
GO
CREATE PROC GetXMLData @xmlDocument xml,@Path varchar(256),@TableName varchar(256)
AS
BEGIN
DECLARE @sql VArCHAR(MAX)
IF OBJECT_ID(N'Temp_tab',N'U') IS NOT NULL
DROP TABLE Temp_tab
SET @sql='select * into Temp_tab from '+@TableName+';'
exec (@sql)
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
SELECT * FROM OPENXML(@docHandle, @Path) WITH Temp_tab
-- Use OPENXML to provide rowset consisting of order data.
drop table Temp_tab
END
exec GetXMLData '<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25 00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03 00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>','/ROOT/Customers/Orders','Orders'