salve,
se ho ben compreso, ogni riga della tabella contiene un frammento XML destrutturato che puo' contenere N elementi "Valore" contrassegnati da 1 a N;
tu vuoi una restituzione tabellare, per ogni riga, di tutti i subelementi contenuti nel frammento XML.
se questo e' vero, potremmo intraprendere la strada di una ricostruzione tabellare tramite una tradizionale proiezione del frammento xml per poi effettuare un UNPIVOT... devi pero' a priori conoscere la cardinalita' di N al fine di poterla recuperare... nell'esempio di cui sotto, ho "stabilito" che la profondita' sara' da 1 a 4, e ne restituisce, per Valore da 1 a 4 il relativo valore o eventualmente NULL in sua assenza, per poi, nuovamente conoscendo a priori la cardinalita', effettuarne l'UNPIVOT...
fatto questo, ci e' semplice definire una funzione utente che ritorni una tabella per tutte le righe ristrutturate del frammento xml per operare quindi un CROSS APPLY per ogni riga della tabella originale...
il risultato sara' poi facilmente messo in join con le tabelle referenziate relative..
ad esempio,
SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @x1 xml='<TAG1>
<TAG2>
<CODICE>ALIM</CODICE>
<DESCRIZIONE>Alimentatore</DESCRIZIONE>
<VALORE1>1430</VALORE1>
</TAG2>
<TAG2>
<CODICE>PC</CODICE>
<DESCRIZIONE>PC</DESCRIZIONE>
<VALORE1>45464</VALORE1>
</TAG2>
<TAG2>
<CODICE>MTH</CODICE>
<DESCRIZIONE>MOTHERBOARD</DESCRIZIONE>
<VALORE1>500</VALORE1>
</TAG2>
</TAG1>';
DECLARE @x2 xml='<TAG1>
<TAG2>
<CODICE>ALIM</CODICE>
<DESCRIZIONE>Alimentatore</DESCRIZIONE>
<VALORE1>1430</VALORE1>
<VALORE2>130</VALORE2>
</TAG2>
<TAG2>
<CODICE>PC</CODICE>
<DESCRIZIONE>PC</DESCRIZIONE>
<VALORE1>45464</VALORE1>
<VALORE2>14730</VALORE2>
</TAG2>
<TAG2>
<CODICE>MTH</CODICE>
<DESCRIZIONE>MOTHERBOARD</DESCRIZIONE>
<VALORE1>500</VALORE1>
<VALORE2>1330</VALORE2>
</TAG2>
</TAG1>';
PRINT 'SELEZIONE DIRETTA';
SELECT
ref.value('CODICE[1]', 'NVARCHAR (20)') AS CODICE,
ref.value('DESCRIZIONE[1]', 'NVARCHAR (20)') AS DESCRIZIONE,
ref.value('VALORE1[1]', 'int') AS Valore1,
ref.value('VALORE2[1]', 'int') AS Valore2,
ref.value('VALORE3[1]', 'int') AS Valore3,
ref.value('VALORE4[1]', 'int') AS Valore4
--, ......
FROM @x1.nodes('/TAG1/TAG2') xmlData( ref )
ORDER BY Codice;
PRINT 'UNPIVOTING';
WITH cte AS (
SELECT
ref.value('CODICE[1]', 'NVARCHAR (20)') AS CODICE,
ref.value('DESCRIZIONE[1]', 'NVARCHAR (20)') AS DESCRIZIONE,
ref.value('VALORE1[1]', 'int') AS Valore1,
ref.value('VALORE2[1]', 'int') AS Valore2,
ref.value('VALORE3[1]', 'int') AS Valore3,
ref.value('VALORE4[1]', 'int') AS Valore4
--, ......
FROM @x1.nodes('/TAG1/TAG2') xmlData( ref )
)
SELECT CODICE, DESCRIZIONE, ItemVal
FROM cte c
UNPIVOT
(ItemVal FOR Valore IN (Valore1, Valore2, Valore3, Valore4) ) as tblunpvt;
GO
CREATE TABLE dbo.t ( id int NOT NULL IDENTITY, x xml NOT NULL );
INSERT dbo.t
VALUES
('<TAG1>
<TAG2>
<CODICE>ALIM</CODICE>
<DESCRIZIONE>Alimentatore</DESCRIZIONE>
<VALORE1>1430</VALORE1>
</TAG2>
<TAG2>
<CODICE>PC</CODICE>
<DESCRIZIONE>PC</DESCRIZIONE>
<VALORE1>45464</VALORE1>
</TAG2>
<TAG2>
<CODICE>MTH</CODICE>
<DESCRIZIONE>MOTHERBOARD</DESCRIZIONE>
<VALORE1>500</VALORE1>
</TAG2>
</TAG1>'),
('<TAG1>
<TAG2>
<CODICE>ALIM</CODICE>
<DESCRIZIONE>Alimentatore</DESCRIZIONE>
<VALORE1>1430</VALORE1>
<VALORE2>130</VALORE2>
</TAG2>
<TAG2>
<CODICE>PC</CODICE>
<DESCRIZIONE>PC</DESCRIZIONE>
<VALORE1>45464</VALORE1>
<VALORE2>14730</VALORE2>
</TAG2>
<TAG2>
<CODICE>MTH</CODICE>
<DESCRIZIONE>MOTHERBOARD</DESCRIZIONE>
<VALORE1>500</VALORE1>
<VALORE2>1330</VALORE2>
</TAG2>
</TAG1>');
GO
CREATE FUNCTION dbo.ufn_UnpivotArticle (
@Id int
)
RETURNS @ret TABLE
(
Id int,
CODICE nvarchar(20),
DESCRIZIONE nvarchar(20),
ItemVal int
)
AS BEGIN
DECLARE @x xml;
SELECT @x = x
FROM dbo.t
WHERE Id = @Id;
WITH cte AS (
SELECT
ref.value('CODICE[1]', 'NVARCHAR (20)') AS CODICE,
ref.value('DESCRIZIONE[1]', 'NVARCHAR (20)') AS DESCRIZIONE,
ref.value('VALORE1[1]', 'int') AS Valore1,
ref.value('VALORE2[1]', 'int') AS Valore2,
ref.value('VALORE3[1]', 'int') AS Valore3,
ref.value('VALORE4[1]', 'int') AS Valore4
--, ......
FROM @x.nodes('/TAG1/TAG2') xmlData( ref )
)
INSERT INTO @ret
SELECT @Id AS Id, CODICE, DESCRIZIONE, ItemVal
FROM cte c
UNPIVOT
(ItemVal FOR Valore IN (Valore1, Valore2, Valore3, Valore4) ) as tblunpvt;
RETURN
END;
GO
SELECT t.id
, ca.CODICE, ca.DESCRIZIONE, ca.ItemVal
FROM dbo.t t CROSS APPLY dbo.ufn_UnpivotArticle (t.id) ca
WHERE t.id = 1
SELECT t.id
, ca.CODICE, ca.DESCRIZIONE, ca.ItemVal
FROM dbo.t t CROSS APPLY dbo.ufn_UnpivotArticle (t.id) ca
ORDER BY t.id;
GO
DROP FUNCTION dbo.ufn_UnpivotArticle;
DROP TABLE dbo.t;
--<--------
SELEZIONE DIRETTA
CODICE DESCRIZIONE Valore1 Valore2 Valore3 Valore4
-------------------- -------------------- ----------- ----------- ----------- -----------
ALIM Alimentatore 1430 NULL NULL NULL
MTH MOTHERBOARD 500 NULL NULL NULL
PC PC 45464 NULL NULL NULL
UNPIVOTING
CODICE DESCRIZIONE ItemVal
-------------------- -------------------- -----------
ALIM Alimentatore 1430
PC PC 45464
MTH MOTHERBOARD 500
id CODICE DESCRIZIONE ItemVal
----------- -------------------- -------------------- -----------
1 ALIM Alimentatore 1430
1 PC PC 45464
1 MTH MOTHERBOARD 500
id CODICE DESCRIZIONE ItemVal
----------- -------------------- -------------------- -----------
1 ALIM Alimentatore 1430
1 PC PC 45464
1 MTH MOTHERBOARD 500
2 ALIM Alimentatore 1430
2 ALIM Alimentatore 130
2 PC PC 45464
2 PC PC 14730
2 MTH MOTHERBOARD 500
2 MTH MOTHERBOARD 1330
se, invece, non ho compreso niente, allora.... :)
saluti
