XML data import to SQL

milenalukic

I have an XML document with parent and 3 different chile elements. Does anyone know how to get this into SQL please? Might be a silly question but it's the first time for me. :doh:

jschell

Probably depends on what you really mean. But some possible solutions. 1. MS SQL Server (TSQL)can process XML. So write some TSQL to do it. 2. Write some C# (or Java, C++, perl, etc) which parses the XML and outputs in to import files. Then use the inport feature of the database to import it. Myself I would generally always chose 2 because I am more familar with doing it with something besides TSQL and because then I don't need to worry about impacting database performance while it runs.

PIEBALDconsult

If the XML is something like:

and I don't yet a table for it, then my preference is to read the XML into an XmlDocument, iterate the records, iterate the children, cobble up appropriate CREATE TABLE and INSERT statements, execute the CREATE TABLE, then iterate the document again executing the INSERT for each record. I have a console application to do this.

milenalukic

The XML data will be put on my ftp server and I need to read the data. I have created the tables in SQL but now need to write a stored procedure to read the XML. All examples I found only use the 1 table so I still haven't managed to get the data into multiple tables. Also in the child elements there is a field that include the foreign key [/int] added to it. I suppose I could do a substring once I read the data but for now I'm still stuck.