I want to load XML to a table in SQL Server 2005

10 pts.
Tags:
SQL Server 2005
SQL Server import/export
XML
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

You use the OPENXML command to get the XML into a tabular form. This is a very basic example but it shows the syntax.

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.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,065 pointsBadges:
    report
  • Meshall
    Mrdenny Could you please show an example how you would Process putting the temp table from BULK INSERT into a varible so the OPENXML could put it in a table?
    10 pointsBadges:
    report
  • Denny Cherry
    Meshall, I'm not sure what you are looking for here. This article I wrote a while back may be what you are looking for. Denny
    66,065 pointsBadges:
    report
  • Ana63
    Good morning Mrdenny, You may be helping me with load xml file to SQL SERVER 2008. I am new with SSIS, still learn. But I have xml files load to one of direcory ones a week with different extensions I already have SSIS create for this process, but I do not know how I can do automization. Can you please give me advice? Thank you, Ana
    35 pointsBadges:
    report
  • Denny Cherry
    Ana63, If all you need to do is schedule the package to run, save the package to the server, then create a SQL Agent job which calls the SSIS package. You've already done the hard part.
    66,065 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following