How to Relate a Table to Another Table where the field to relate is an XML

Question

I have an SQL Server database from a system that has been discontinued. In this database, there is a table MOVIMENTACAO and a table EQUIPAMENTO, where would set up a relationship N:N.

The problem is that instead of having a third table, there is a field in the table MOVIMENTACAO with the Ids of EQUIPAMENTOS format XML.

EX:

<ArrayOfInt xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <int>39254</int>
  <int>39255</int>
  <int>79349</int>
  <int>79351</int>
  <int>79354</int>
  <int>79456</int>
  <int>79539</int>
  <int>79547</int>
</ArrayOfInt>

Doubt: Is there any way to assemble a QUERY where I can make that relationship?

Answer 1

The best way would be to normalize these data in an N:N generated database and change the application code to handle this new structure.

If that’s not possible, we can do the following:

TABLE MOVES

EQUIPMENT TABLE

Now we need to create a script that normalizes XML in lines (equipment) along with their respective drives.

/*Tabela virtual para armazenar a relação N:N*/
DECLARE @TAB_NORMALIZADA TABLE(
  cod_movimentacao int,
  cod_equipamento int
)

/*Normalização dos dados*/
INSERT INTO @TAB_NORMALIZADA
SELECT 
  cod_movimentacao,
  T2.lin.value('(.)[1]', 'varchar(100)')
FROM   MOVIMENTACOES
CROSS APPLY xml_equipamentos.nodes('/ArrayOfInt/int') as T2(lin)

/*Select com nome do equipamento e código de movimentação*/
SELECT 
  T.* ,
  E.equipamento
FROM @TAB_NORMALIZADA T INNER JOIN EQUIPAMENTOS E
ON T.cod_equipamento = E.cod_equipamento

If you can change the database structure, create a new table to replace my virtual table and a Trigger in the drive table to popular this new table.