You will want to use the OPENXML command to get the data from the XML document.
First use the BULK INSERT command to load the entire XML document into a temporary table. You are going to put the entire XML Document into a single field of a single record.
<pre>CREATE TABLE #XMLData
BULK INSERT #XMLData FROM 'D:YourXMLFile.xml' WITH BULK</pre>
Once you have done this you can load the XML data from the table into an XML variable. From there you can use the sp_xml_preparedocument, then the OPENXML command to view the data.
<pre>DECLARE @hDoc INT
DECLARE @XML XML
SELECT @XML = XMLData
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
INSERT INTO YourTable
FROM OPENXML (@hDoc, '/root/YourPath')
WITH (YourId INT '@IdField',
LastName VARCHAR(40) '@LastName')
EXEC sp_xml_removedocument @hDoc
Make sure you remove the document from the XML DOM using the sp_xml_removedocument procedure. Otherwise SQL Server will keep the XML Document in memory.
Last Wiki Answer Submitted: September 8, 2008 6:44 am by Denny Cherry64,520 pts.