I need to import xml file into Microsoft SQL Server database. And I have a xml schema file (.xsd) that contains the mapping info for the xml file to be imported. Each valid data line in xml file need to be imported into a database table row with several columns.
Originally I was using SQLXMLBulkLoad (Non Transaction Mode - specify a database connection string) to do the job and it works just fine, however, there is a new requirement that need me to execute a stored procedure on the same database connection that SQLXMLBulkLoad uses. The purpose of this stored procedure is to bind the connection with a specific ID for the current user because there might be multiple users importing xml file at the same time. So I have to use transaction mode instead (- specify a database connection rather than a connection string for SQLXMLBulkLoad) and which introduce the well known share issue under remote environment. If the xml file is local and the db server is remote, I need to create a shared folder for SQLXMLBulkLoad to work. This is bad but can be done.
The problem is the share folder thing is not accepted by our clients, so I need a better solution that could satisfy the new requirement without the share issue.
I've Googled a while and found one solution that might work - SqlBulkCopy, but I have trouble with parsing the schema file (.xsd) because it contains many xml-db:table mapping information.
Any idea would be appreciated.