Xml file into sql server 2005

The first is to grab some file attributes and the second is to move files around. The file attribute procedure will receive 3 parameters, path to an archive directory, root path to grab files from and the work table to initially insert the data.

We use a work table for the simple task of ensuring we do not get files into the main table multiple times. Basically they are identical but one I have plans to ship offsite for recovery purposes. The path parameter is important for archiving the files. This way they go to normal tape backups and can be restored with the slower process of file backup services.

All the files will be moved accordingly to these folders once they are imported into our table and any other needed processing performed. You can manage that directory structure simply by doing …. Once we do this we know we have our directory structure. Sense this process will run under a scheduled SQL Agent job we know the SQL service credentials have access to the local folder structure.

Please secure these types of processes. Everyone knows my push for security and not turning your head simply when things are easier to not set them up securely. More error handling should be added also over these examples. You can use my code but make sure you actually test it under pressure and scenarios that can affect true production systems. Next step is to enumerate the XML files in the directory. That again is a pretty simple task and can be done in many methods.

I used the Direcotory. Checked could only find bulk export and import to and from XML files. September 4, at pm September 5, at am September 6, at am Yeah, my mind went to SSIS, will have a go at it and post my findings here. September 15, at am August 11, at pm Try using BCP. It worked for me and I used the output to create a schema. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta.

New post summary designs on greatest hits now, everywhere else eventually. Related Hot Network Questions. Question feed. Stack Overflow works best with JavaScript enabled. Accept all cookies Customize settings.