Using XML Documents
Now consider the following example.
CREATE PROCEDURE SP_OpenXML_Example
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
N’
SELECT *
FROM OPENXML (@hDoc, ‘/ROOT/Customers/Orders’,1)
WITH (CustomerID varchar(10),
ContactName varchar(20) ‘../@ContactName’,
OrderDate DateTime)
EXEC sp_xml_removedocument @hDoc
In the above example the XML Document of Customer Details is made to create an in-memory representation of the XML document of customer Details using the sp_xml_preparedocument system stored procedure. The Output parameter @hdoc holds the pointer to the in-memery representation of the XML Document.
Now to query the Contents of the XML Document OPENXML clause is introduced in SQL Server 2000. The OPENXML Clause has the following parameter one the Pointer to the in-memory representation of the XML Document, Two the Xpath of the XML Document to get the required Data and Three the flags to Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. Using WITH Clause we need to specify the columns and their position in the XML Document. In this Ex. The Xpath is given till the OrdersNode ‘/ROOT/Customers/Orders’ so the column CustomerID is taken from attribute CustomerID as default since the column name and the node name are the same. ContactName column is taken from the Parent Node Customers. OrderDate is from the Same Node. And finally to remove the in-memory represenation of the XML Document the sp_xml_removedocument is executed with the pointer as input parameter.