Doing A migration work recently, is to A table (please call table A) data mentioned is inserted into the other table, but more difficult is the most of the data in table A in the XML format column B, in which I try to use SQL to provide value (), the query () method of layer by layer loop to read the value of the inside, but the performance has been poor (table A is quite big, may have tens of dozens of G) records, want to ask about you, is there any way I can read XML format of the data more efficiently?

In addition to the loop, I have also tried using OPENXML attempt one-time read all of the data, but ran into A specific problem: field is made up of name_X1 - X2, (X1, X2) records required in table B, (name) records need to put in table C, also want to ask about you and there were no methods can take apart the two values... (field A is the inside of the XML format to record the value of A tag)

CodePudding user response:

If you take a data, can increase a persistable computed columns,
If you want to get more data, and is not fixed, there is no good way,

CodePudding user response:

First reads all the data in a table, and then to break up,
It is better to offer some sample data, the original what, what after the migration,