Extracting data from XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Extracting data from XML

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-18 : 02:00:22

Is there any way I can extract the contents of a XML file.Will DTS help to extract the dataset.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-18 : 21:45:42

Should we use some other tools for getting dataset out of xml

Kristen
Test

22859 Posts

Posted - 2004-09-19 : 01:40:01

Sorry, don't understand the question

Have you got data in SQL that you want to export in an XML file?

Or an XML file that you want to import into SQL table(s)?

Or an XML file that you want to query, using SQL syntax, without importing into a SQL table?

Thanks!

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-20 : 12:18:16

Hi Kristen,
I have an XML file that i want to import into sql tables.Also in one section I have to export to an XML file from SQL tables.Iam haven't used the XML SQl combination.It will be greatful if u can help me with this

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 14:03:46

Import can be done with something like:


DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
      N'<ROOT>
<MyTable>
    <MyColumn1>9</MyColumn1>
    <MyColumn2>2004-06-30T06:55:20.040</MyColumn2>
</MyTable>
</ROOT>'

INSERT INTO MyTable
SELECT * 
FROM OPENXML(@hDoc, N'/ROOT/MyTable', 2) 
     WITH MyTable

EXEC sp_xml_removedocument @hDoc

SELECT * FROM MyTable

Krsiten

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-21 : 01:48:36

Thanks a lot.. let me go ahead and do some research and then I may have more things to ask u..But really thanks a lot kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources