XML dans SQL Server - Compter le nombre de noeuds

Version imprimable

14/03/2017, 12h04
maoboyindo

XML dans SQL Server - Compter le nombre de noeuds

Bonjour,

Je débute en SQL Server et XML.

J'ai 2 champs au format XML qui me permettent d'identifier les mises à jour de données (Colonne ancienne valeur et une colonne nouvelle valeur).

Il va me falloir comparer les deux attributs pour identifier ce qui a changé.

Auriez-vous du code exemple ?

A défaut, est-ce-que quelqu'un saurait me dire comment compter le nombre de noeud et attributs ?

Merci beaucoup d'avance :zoubi:
14/03/2017, 17h21
SQLpro

Utilisez la fonction XQuery count. Exemple :

Code:

count(/toto/tata:elementA)

A +
14/03/2017, 17h32
maoboyindo

Merci mais ....

Merci mais quand on ne connaît pas la structure à l'avance, les choses se compliquent ....

Et surtout, comment inclure cette instruction dans une commande SQL Server ?

En fait, mes données sont stockées dans des champs XML.
Une colonne OLDVALUE XML et une colonne NEWVALUE XML.

J'ai besoin de les comparer et de mettre en évidence les différences :
Quel attribut est différent et quelle est la valeur avant (OLDVALUE) et après (NEWVALUE).

Merci beaucoup d'avance.

:?
14/03/2017, 18h13
SQLpro

Donnez un exemple du XML...

Vous pouvez utiliser la fonction fn:node-name()

A +

Voilà la requête que je lance
Code:

1 2 3 select H.* from XX.Historisation AS H
Et voilà le résultat :

Colonne OldValue :
Code:

1 2 3 4 5 6 7 8 <ItemMaster> <ItemMaster> <ID_IM>E9812B30-935D-42F9-A174-5C82815E64AD</ID_IM> <Reference>TESTSMA4</Reference> <Description>Test SMA 4 - Description</Description> <IMLOG>36</IMLOG> </ItemMaster> </ItemMaster>
Colonne NewValue :
Code:

1 2 3 4 5 6 7 8 9 <ItemMaster> <ItemMaster> <ID_IM>E9812B30-935D-42F9-A174-5C82815E64AD</ID_IM> <Reference>TESTSMA4</Reference> <Description>Test SMA 4 - Description ++</Description> <IMLOG>36</IMLOG> </ItemMaster> </ItemMaster>
Via une fonction SQL, je voudrais mettre en évidence le fait que la valeur de la description change entre le old et le new.

A noter que cette table d'historisation est utilisée pour plusieurs tables.
Donc la structure de l'XML diffère à chaque fois.

C'est pour cela que j'ai besoin de déterminer le nombre de nœuds.

Merci d'avance.

Déjà vous avez un problème car des nœuds de hiérarchies différentes ont le même nom ce qui n'est pas très bon ! L'un devrait être au pluriel par exemple et l'autre au singulier.

Ce serait aussi important que vous respectiez la charte de postage en donnant le DDL de vos tables et le jeu d'essais ! A lire : https://www.developpez.net/forums/d9...vement-poster/

Donc, voici le jeu d'essai qui m'a servit :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE Historisation (ID INT, OldValue XML, NewValue XML);
 
INSERT INTO  Historisation VALUES (1,
'<ItemMaster>
  <ItemMaster>
    <ID_IM>E9812B30-935D-42F9-A174-5C82815E64AD</ID_IM>
    <Reference>TESTSMA4</Reference>
    <Description>Test SMA 4 - Description</Description>
    <IMLOG>36</IMLOG>
  </ItemMaster>
</ItemMaster>',
'<ItemMaster>
  <ItemMaster>
    <ID_IM>E9812B30-935D-42F9-A174-5C82815E64AD</ID_IM>
    <Reference>TESTSMA4</Reference>
    <Description>Test SMA 3 - Description ++</Description>
    <IMLOG>38</IMLOG>
  </ItemMaster>
</ItemMaster>');

Et la requête finale :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH 
T1 AS
(
SELECT H.ID, O.x.value('local-name(.)', 'varchar(128)') AS PRMO,
             O.x.value('(.)[1]', 'varchar(50)') AS VALO
FROM   Historisation AS H
       CROSS APPLY OldValue.nodes('/ItemMaster/ItemMaster/*') AS O(x)
),
T2 AS
(
SELECT H.ID, N.x.value('local-name(.)', 'varchar(128)') AS PRMN,
             N.x.value('(.)[1]', 'varchar(50)') AS VALN
FROM   Historisation AS H
	   CROSS APPLY NewValue.nodes('/ItemMaster/ItemMaster/*') AS N(x)
)
SELECT *
FROM   T1
       INNER JOIN T2
	         ON T1.ID = T2.ID AND T1.PRMO = T2.PRMN AND T1.VALO <> T2.VALN ;

Il serait intéressant que vous vous formiez a XQuery/XPath en sus de SQL et de son intégration au sein des requêtes SQL !

A +

Merci beaucoup !!!!

Cependant, ceci n'est valable que si l'on connaît à l'avance le nom des nœuds ....

Voici la structure de ma table :

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE [XX].[Historisation]
   ([ID] UNIQUEIDENTIFIER NOT NULL,
    [TableSource] varchar(100) NULL,
	[OldValue] xml NULL,
	[NewValue] xml NULL,
	[TypeMvt] varchar(10) NULL,
	[DateMvt] datetime2 NOT NULL,
	[AuthorMvt] varchar(50) NOT NULL,
	[ComputerName] varchar(50) NOT NULL DEFAULT host_name(),
	[Provenance] varchar(50) NOT NULL DEFAULT app_name()
	)

Je vais donc brancher un trigger sur les tables que j'ai besoin d'historiser avec le code suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE OR ALTER TRIGGER [RA].[TrgHistorisationMaTable]
	ON [XX].[MaTable]
	AFTER INSERT, DELETE, UPDATE
AS 
	BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;
 
		-- Cas d'insertion
		IF NOT EXISTS(SELECT * FROM deleted) 
			BEGIN
				INSERT INTO [XX].[Historisation]
					([ID], TableSource, OldValue, NewValue,
					 TypeMvt, DateMvt, AuthorMvt)
					SELECT [IDFam], 'MaTable', NULL, (SELECT * FROM inserted where inserted.IDFam = I.IDFam FOR XML PATH('MaTable'), ELEMENTS, ROOT('MaTable')),
						   'INSERT', SYSDATETIME(), UPPER(SUSER_SNAME())
					FROM inserted AS I;
			END;
		-- Cas de suppression
		IF NOT EXISTS(SELECT * FROM inserted)
			BEGIN
				INSERT INTO [XX].[Historisation]
					([ID], TableSource, OldValue, NewValue,
					 TypeMvt, DateMvt, AuthorMvt)
					SELECT [IDFam], 'MaTable', (SELECT * FROM deleted where deleted.IDFam = D.IDFam FOR XML PATH('MaTable'), ELEMENTS, ROOT('MaTable')), NULL,
						   'DELETE', SYSDATETIME(), UPPER(SUSER_SNAME())
					FROM deleted AS D;
			END;
 
		-- Cas de la modification
		INSERT INTO [XX].[Historisation]
			([ID], TableSource,
			 OldValue,
			 NewValue,
			 TypeMvt, DateMvt, AuthorMvt)
			SELECT D.[IDFam], 'MaTable',
				   (SELECT * FROM deleted where deleted.IDFam = D.IDFam FOR XML PATH('MaTable'), ELEMENTS, ROOT('MaTable')),
			       (SELECT * FROM inserted where inserted.IDFam = I.IDFam FOR XML PATH('MaTable'), ELEMENTS, ROOT('MaTable')),
				   'UPDATE', SYSDATETIME(), UPPER(SUSER_SNAME())
			FROM deleted AS D
			INNER JOIN inserted AS I ON I.IDFam = D.IDFam;
	END
GO

15/03/2017, 12h42
SQLpro

Citation:

Envoyé par maoboyindo

Merci beaucoup !!!!

Cependant, ceci n'est valable que si l'on connaît à l'avance le nom des nœuds ....

Ben non !!!!

Vous n'avez visiblement pas compris...

À aucun moment je n'ai mis le nom des "colonnes" de vos données historisées...

Tant qu'à faire, au lieu de faire une table d'historisation par table source il serait plus intéressant de regrouper le tout dans une seule table.

Lisez l'article que j'ai écrit à ce sujet :
https://blog.developpez.com/sqlpro/p...on_des_donnees

A +