10 pts.
0
Q:
Is there a built-in stored procedure in SQL Server 2000 that returns the sql table structure of XML format
i have a xml file.i want to insert the contents of the xml file into a table in sql server 2005.
ASKED: Sep 8 2008  5:33 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0
47070 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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.
Last Answered: Sep 8 2008  6:44 AM GMT by Mrdenny   47070 pts.
0
0
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Mrdenny   47070 pts.  |   Sep 8 2008  6:44AM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

 
0