Requête SQL sur champ XML

Version imprimable

15/03/2011, 11h56
elflamby

Requête SQL sur champ XML
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:

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:

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.

Bonjour,

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

Code:

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:

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:

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:

1
2
SELECT	*
FROM	dbo.mesValeurs

Qui vous retournera :

http://img69.imageshack.us/img69/811/95629147.png

@++ ;)

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:

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.

Bonjour,

inspirez vous de cet exemple

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
 
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

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

Code:

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 :)

Oh nice !!

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

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 ? :)

15/03/2011, 13h43
aieeeuuuuu
Citation:

Envoyé par elflamby

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:

1 2 3 SELECT Colonne1, Colonne2,... FROM ...
que le
Code:

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
15/03/2011, 14h53
elflamby

Je note tous ces bons conseils merci :)

Oui pour le type pour l'instant le moment test (car varchar 50 n'est pas suffisant niveau taille :p)
15/03/2011, 15h48
elsuket

Citation:

Citation:

Envoyé par elflamby Voir le message
Oh nice !!

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

Le CROSS APPLY, de loin !

D'excessivement loin même ! C'est ce que je démontre ici
Quand vous codez en SQL, oubliez la logique ligne-à-ligne, puisque SQL est un langage ensembliste.
D'autre part, SQL étant aussi un langage déclaratif, il ne vous est nécessaire de spécifier que ce que voulez obtenir, pas comment vous voulez l'obtenir : SQL Server sait le faire bien mieux que vous ;)

@++ ;)