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.
Software/Hardware used:
Software/Hardware used:
ASKED:
September 8, 2008 5:33 AM
UPDATED:
September 8, 2008 12:33 PM
RELATED QUESTIONS
- I want to load XML to a table in SQL Server 2005
- How to create an XML File from typed XML data stored in Multiple Rows and is referring to same XSD
- export xml file of new stored data into a folder through trigger in sql server!
- Problems exporting Access table to XML file
- get the value from XML in a sql table
Answer Wiki:
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
(XMLData XML)
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
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</pre>
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.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.