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.

CREATE TABLE #XMLData
 (XMLData XML)
 
 BULK INSERT #XMLData FROM 'D:\YourXMLFile.xml' WITH BULK

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.

DECLARE @hDoc INT
 DECLARE @XML XML
 
 SELECT @XML = XMLData
 FROM #XMLData
 
 EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
 
 INSERT INTO YourTable
 SELECT *
 FROM OPENXML (@hDoc, '/root/YourPath')
 WITH (YourId INT '@IdField',
 LastName VARCHAR(40) '@LastName')
 
 EXEC sp_xml_removedocument @hDoc
 
 GO

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.

Please complete the following information:

REGISTER / LOG-IN TO DISCUSS

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Submit your e-mail address below to continue

E-mail: 

E-mail: 

Handle:   

Password: 

Forgot Password?

Create Handle: 

Your handle identifies you in ITKnowledgeExchange. Max 30 char.

Create Password: 

Confirm Password: 

Yes, I agree to the IT Knowledge Exchange  Terms and Conditions.

hidden modal window