Greetings

There is a task to import a number of DBF directories in MS SQL Server 2008 from a C # application. The situation is complicated by the fact that I can only import data through stored procedures on the server, since the application itself may not have rights to work directly with the tables to which data should be added. Therefore, for example, System.Data.SqlClient.SqlBulkCopy not very suitable, as well as direct queries via SqlCommand . At once I will say that there is a restriction on the EXEC command on the server, i.e. dynamic queries will not take off.

Now there are about 12 XML files in which the data converted from DBF lie, this is the source. The receiver uses 12 tables in the MS SQL database.

At the same time, the structure of source tables and destination tables do not match (types, field names). This is due to the fact that in XML the data is stored without explicitly indicating the type ( yes, it would be possible to make a scheme in XSD, but this is not necessary, because in the original files, for example, the values ​​of type int all the time stored in char , t. that is, the source field is of type char , the receiver must have an int ).

The field names do not match for the reason that they were just crookedly named in the DBF, and we didn’t want to transfer it to MS SQL ( for example, the city name in the directory is stored in the field with the name I_GOR, in the SQL database - the same field is named as Name ) .

But here the number of fields of any source table coincides with the number of fields of a similar table-receiver.

Therefore, the solution is seen as a single stored procedure, let 'sp_ImportXMLDictionary', which would be given as input as parameters: the name of the table into which to import and the XML file to be imported. The procedure would take data from each XML column, and simply insert it into the target table in a column with the same sequence number.

Example:

Initially, DBF contains data fields:

 COD_RS (char), GOR (char), I_GOR (char) 55 000 Нью-Васюки 56 002 Гадюкино 56 007 Васюки

After processing, we get the next set of fields (with data) in XML

 <?xml version="1.0" standalone="yes"?> <DocumentElement> <SPRGOR> <ID>1</ID> <COD_RS>55</COD_RS> <GOR>000</GOR> <I_GOR>Нью Васюки</I_GOR> </SPRGOR> <SPRGOR> <ID>2</ID> <COD_RS>56</COD_RS> <GOR>002</GOR> <I_GOR>Гадюкино</I_GOR> </SPRGOR> <SPRGOR> <ID>3</ID> <COD_RS>56</COD_RS> <GOR>007</GOR> <I_GOR>Васюки</I_GOR> </SPRGOR> </DocumentElement>

The target table has the structure:

 ID (int, identity, PK), AreaCode (int), CityCode (int), Name (nvarchar(50))

Ideally, I would like to make the first field from XML fall into the first field of the table (ID ---> ID), the second from XML into the second field of the table (COD_RS ---> AreaCode) and so on, according to the number of fields . And yes, with the conversion of data when inserted into the data type specified in the receiver.

Your opinion, how best to solve this puzzle? Maybe someone came across this?

All that I have seen so far is the insertion of data into the table, either taking into account the structure of the tables, or it is proposed to use a wizard for importing (with mapping fields and types), but using the wizards is not suitable initially, and tied to the structure of each of the source tables and writing for each of them I would not very much like my own (more precisely, I would not really like it ).

(I have already done the XML generation, the problem is in the subsequent download of this data to the server)

  • 2
    1. I understand that this is a one-time task. So you should use the import wizard and not suffer. 2. What do you mean no rights? It is strange that there are rights to create (not use ) a store, but there are no rights to use regular queries. Give yourself rights and again do not suffer. 3. Sometimes it is easier to write a dozen simple procedures than one complex. Wrote - fulfilled - threw out. - Alexander Petrov
  • one
    Pass the XML string as a parameter to the procedure, and then read it as XML, see . Such a thing has been available since the 2000th. - mals
  • @AlexanderPetrov, 1. No, not a one-time, but periodic, once a month. 2. I can create storages on a test database, everything is different in production, the created scripts will be filled with DBA once. 3. Sometimes - yes, but not in this particular case, unfortunately (a similar procedure is subsequently planned to be used at least in several more places). @MaLS, thanks, I’ll pass on the XML to the procedure already well, because for example this is: SET @xml = ( SELECT * FROM OPENROWSET ( BULK 'D:\MyXMLData.xml', SINGLE_CLOB ) AS xml gives error 21 ( no rights). - BlackWitcher
  • one
    a) In the target table ID int identity , and you want to insert an ID ---> ID , for this you will need to enable set identity_insert [TableName] on , but you need rights to do that. If I were a DBA, I would not allow this (in the context of the problem, this seems abnormal). b) I would not discard SqlBulkCopy (you can create a temporary table, use SqlBulkCopy to import data into it, and in the procedure from a temporary table to transfer data to the target table). However, import from xml is also quite a good option. c) The sp_ prefix for procedures is not recommended. - i-one Nov.
  • one
    By prefix: sp_ - link 1 , link 2 , tbl_ - link . - i-one

1 answer 1

Here is a ready-made example of code that will not correct problems for this case:

https://stackoverflow.com/questions/9672780/importing-xml-data-from-xml-file-to-sql-database

  • Yes, thanks, I did it in a similar way, via XPath. XML is thrown as a string to the server, and in a stored procedure it is already parsed as a table in exactly the same way. I accept your answer, as it most closely matches reality) - BlackWitcher