Mrdenny 
46795 pts. | Aug 29 2008 11:13AM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Q:
I want to load XML to a table in SQL Server 2005
i have a xml file.now i should upload the tags of the xml as fileds in a table in sql server.automatically the values should be inserted in the table data.
ASKED:
Aug 29 2008 8:58 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
DECLARE @xml AS XML
DECLARE @hDoc AS INT
SET @xml = '<root><data name="value" /></root>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
SELECT *
FROM OPENXML (@hDoc, '//')
WITH (name NVARCHAR(10) '@name') AS a
EXEC sp_xml_removedocument @hDoc
GO
If you have the XML data in an XML file you can use the BULK INSERT command to load the data into a temp table then get the value from the temp table and put it into a variable for processing by the OPENXML command. If you need an example of that let me know.
