Import XML to SQL - 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

Import/Export (DTS) and Replication (2000)

Import XML to SQL

Author

Topic

billsack
Starting Member

35 Posts

Posted - 2005-11-08 : 04:56:41

Hello folks,

I am having some real trouble trying to understand how to import xml to sql server.

I have a data extract from an ordering/supply database that I am trying to get into sql so that we can better analyse and warehouse the data. The trouble is............I haven't got a clue where to start!!!!

I've checked online but cant really fathom what going on. I gather this is quite a complex task. Does anyone know of some straightforward documentation to guide me through the process? Any help would be seriously appreciated!

Thanks very much.

Kristen
Test

22859 Posts

Posted - 2005-11-08 : 06:38:51

Dunno about DTS specifically, but you can basically just insert into a Table direct from some XML. Here's an example for Books Online (you could obviously replace the in-line XML with some external source)


DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, '
<root>
  <Customer>
    <CustomerID>VINET</CustomerID>
    <ContactName>Paul Henriot</ContactName>
  </Customer>
</root>'

INSERT INTO MyCustomerTable
SELECT *
FROM OPENXML (@idoc, '/root/Customer', 1)
      WITH MyCustomerTable

EXEC sp_xml_removedocument @idoc

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources