Discussion :

[elflamby]

Bonjour,

Je cherche à savoir si il est possible de façon simple de compter le nombre d'occurences d'un terme dans un champ XML ou de simplifier mon futur traitement.

Je m'explique : j'ai une table (contenant plus d'un million d'entrés) qui contient un champ id et un champ xml qui ressemble à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
<root>
	<elem attr1="val1" attr2="val2"/>
	<elem attr1="val3" attr2="val4"/>
	<elem attr1="val5" attr2="val6"/>
</root>

je veux créer une table

ID / ATTR1 / ATTR2

Je pensais faire du T-sql en passant par une table temporaire du type

ID / ChampXML / NbOccurences (avec NbOccurence basé sur le nombre de <elem attr1 du champ)

Et ensuite faire une double boucle en utilisant une requête du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Select id
	,ChampXML.value('(root/elem/@attr1)[@i]','nvarchar(max)')
	,ChampXML.value('(root/elem/@attr2)[@i]','nvarchar(max)')
from ma tableTemp

Avec @i de 1 à NbOccurences

Sauf que mise à part passer par une fonction je ne vois pas d'autres solutions pour le nombre d'occurences.

Si quelqu'un a des idées, je suis preneur.
D'avance merci.

[elsuket]

Bonjour,

En utilisant seulement les nœuds, vous pouvez simplement écrire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
DECLARE @xml xml = '<root>
	<elem attr1="val1" attr2="val2"/>
	<elem attr1="val3" attr2="val4"/>
	<elem attr1="val5" attr2="val6"/>
</root>'
 
SELECT	doc.value('./@attr1', 'varchar(32)') AS attr1
	, doc.value('./@attr2', 'varchar(32)') AS attr2
FROM	@xml.nodes('/root/elem') AS X(doc)

Ainsi avec la table suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE mesValeurs
(
	mesValeurs_id INT NOT NULL IDENTITY CONSTRAINT PKmesValeurs PRIMARY KEY
	, ATTR1 varchar(32)
	, ATTR2 varchar(32)
)

Vous pouvez exécuter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @xml xml = '<root>
	<elem attr1="val1" attr2="val2"/>
	<elem attr1="val3" attr2="val4"/>
	<elem attr1="val5" attr2="val6"/>
</root>'
 
INSERT	INTO dbo.mesValeurs
(
	ATTR1
	, ATTR2
)
SELECT	doc.value('./@attr1', 'varchar(32)') AS ATTR1
	, doc.value('./@attr2', 'varchar(32)') AS ATTR2
FROM	@xml.nodes('/root/elem') AS X(doc)

Puis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT	*
FROM	dbo.mesValeurs

Qui vous retournera :



@++

[elflamby]

Bonjour,

Merci pour cette réponse mais, mon soucis c'est que je ne pars pas d'un fichier xml mais déjà d'une table sur ma base remplie et possédant un champ xml lié à un id que je dois conserver.

ex :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
ID 	XML
-------------
1	<root><elem attr1="val1" attr2="val2"/><elem attr1="val3" attr2="val4"/><elem attr1="val5" attr2="val6"/></root>
54	<root><elem attr1="val7" attr2="val8"/><elem attr1="val3" attr2="val4"/><elem attr1="val1" attr2="val9"/></root>
89	<root><elem attr1="val6" attr2="val2"/><elem attr1="val3" attr2="val4"/><elem attr1="val4" attr2="val3"/></root>

Je suppose qu'en modifiant le from c'est faisable ? Mais j'ai un peu de mal à voir la syntaxe.

[aieeeuuuuu]

Bonjour,

inspirez vous de cet exemple

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
DECLARE @T TABLE(
	i INT,
	x XML
	)
 
INSERT INTO @T 
VALUES (1,'<root>
	<elem attr1="val1" attr2="val2"/>
	<elem attr1="val3" attr2="val4"/>
	<elem attr1="val5" attr2="val6"/>
</root>'
),(2,'<root>
	<elem attr1="val1" attr2="val2"/>
	<elem attr1="val5" attr2="val6"/>
</root>'
)
 
--affichage du contenu
SELECT 
	i,
	C.value('@attr1[1]', 'VARCHAR(50)') AS attr1,
	C.value('@attr2[1]', 'VARCHAR(50)') AS attr2
FROM @T
CROSS APPLY x.nodes('root/elem') AS T_XML(C)
 
--affichage du nombre d'elements par ID (ce que vous vouliez ?)
SELECT i, COUNT(*)
FROM @T
CROSS APPLY x.nodes('root/elem') AS T_XML(C)
GROUP BY i

[elflamby]

Au vue des infos j'ai fait un curseur ne voyant pas d'autres solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
DECLARE @id int,@xml xml
DECLARE tmp_cursor CURSOR
FOR select * from base.dbo.TableIn
 
OPEN tmp_cursor
FETCH NEXT FROM tmp_cursor INTO @id,@xml;
 
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO base.dbo.TableOut
	SELECT
		@id	as ID
		,doc.value('./@Name', 'nvarchar(max)') AS [NAME]
		,doc.value('./@Value', 'nvarchar(max)') AS [VALUE]
		,doc.value('./@Type', 'nvarchar(max)') AS [TYPE]
	FROM	@xml.nodes('Properties/Property') AS X(doc)
	FETCH NEXT FROM tmp_cursor INTO @id,@xml;
END
CLOSE tmp_cursor;
DEALLOCATE tmp_cursor;

Si quelqu'un a mieux
En tout cas merci pour votre réponse elsuket, elle me sert comme base du coup à défaut de trouver sans curseur

[elflamby]

Oh nice !!

Merci aieeeuuuuu !
Ça marche nickel, je ne connais pas CROSS APPLY je vais voir du coup à quoi ça correspond

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
 
SELECT 
	ItemPropertyExtendedId,
	C.value('@Name[1]', 'VARCHAR(50)') AS attr1,
	C.value('@Value[1]', 'VARCHAR(50)') AS attr2,
	C.value('@Type[1]', 'VARCHAR(50)') AS attr2
FROM base.dbo.TableIn
CROSS APPLY Properties.nodes('Properties/Property') AS T_XML(C)

Au niveau des performances il vaut mieux privilégier le Curseur ou le CROSS APPLY ?

[aieeeuuuuu]

Oh nice !!

Au niveau des performances il vaut mieux privilégier le Curseur ou le CROSS APPLY ?

Le CROSS APPLY, de loin !

par ailleurs, il vaut aussi mieux privilegier le

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT Colonne1, Colonne2,...
FROM ...

que le

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT *
FROM ...

Enfin, notez que pour l'exemple, j'ai spécifié du VARCHAR(50), mais vous pouvez (et devriez) spécifier le type SQL qui convient en fonction du contenu de votre XML