SQL reads XML fields and sqlxml Fields

SQL reads XML fields and sqlxml Fields

Recently, we have to migrate data to an old system. This is a tough task. We need to compare the fields of the new and old data tables to migrate the data of the old system to the new system.

Although the data structure is inconsistent and some fields are inconsistent, it is just a bit of disgusting and troublesome. It is easy to sort out and write SQL statements for migration.

After several steps, a more disgusting monster came out! Powerful XML data fields

It should have been stored in a sub-Table separately, and stored in an XML field in the old system.

<root>    <people id=1 name=tom/>    <people id=2 name=jim/></root>

During data migration, you must transfer the data to a sub-Table. Therefore, you can read the XML as follows:

IDNAME1TOM1JIM

Start the brain and think about how to break it down?

Let's take two steps. The first step is to read the XML Information of a single piece of data first, and then read the XML of multiple pieces of data. Of course, the data can be read and then inserted, O (∩) O Haha ~

1. Read a single piece of data XML

DECLARE @XML XMLSELECT @XML='<root>    <people id="1" name="tom"/>    <people id="2" name="jim"/></root>'SELECT v.value('@id[1]','VARCHAR(20)') AS ID, v.value('@name[1]','VARCHAR(20)') AS NameFROM @XML.nodes('/root/people') x(v)

2. implement XML reading for multiple data entries

Since each XML data entry may have multiple data entries, here I use a cursor to traverse a piece of data every time, read XML, and then insert and update the self data of the data entry.

DECLARE @ id varchar (36) DECLARE @ xml xmldeclare cur cursor forselect id FROM testTableFOR read onlyopen cur; fetch next from cur into @ id while @ FETCH_STATUS = 0 begin select @ XML = xmlField FROM testTable WHERE id = @ IDSELECT v. value ('@ id [1]', 'varchar (20) ') as id, v. value ('@ name [1]', 'varchar (20) ') AS NameFROM @ XML. nodes ('/root/people') x (v) -- modify the SQL statement to insert or update the SQL statement based on this SQL statement, as a virtual table fetch next from cur into @ IDENDCLOSE CURDEALLOCATE CUR

OK. The problem can be solved here!

Do you have any good solutions for communication. (In the second step, I perform data migration at one time. If it is regular, it is not recommended to use a cursor! You need a better solution...

Reference: http://www.cnblogs.com/l1pe1/archive/2010/07/28/1787254.html

There are a lot of ways to read and operate XML in it. I only use a small part here. If you want to learn updates, you will get a lot from reading the original article!