In SQL Server 2000, you can use a varchar, nvarchar, text, or ntext variable to generate a document handle using the sp_xml_preparedocument stored procedure. In SQL Server 2005, you can also use an xml variable, as shown in the following example.
DECLARE @doc xml
SET @doc = '<?xml version="1.0" ?>
<SalesInvoice InvoiceID="1000" CustomerID="123">
<Items>
<Item ProductCode="12" Quantity="2" UnitPrice="12.99"/>
<Item ProductCode="41" Quantity="1" UnitPrice="17.45"/>
<Item ProductCode="2" Quantity="1" UnitPrice="2.99"/>
</Items>
</SalesInvoice>'
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
SELECT * FROM
OPENXML(@docHandle, 'SalesInvoice/Items/Item', 1)
WITH
(ProductCode int,
Quantity int,
UnitPrice smallmoney)
EXEC sp_xml_removedocument @docHandle
