SQL XML NODES

Version imprimable

SQL XML NODES

Bonjour,

Je reçois une liste en XML à placer en base.

bref je ne maitrise pas les sources et certaines ont les noeuds en majuscule et d'autres en minuscule
voilà le type de noeuds
Code:

1 2 3 4 5 6 7 8 9 10 <SMS> <ID2>48c40c58-8eef-11e6-b3c7-1231420104ed</ID2> <TEL>xxxxxx</TEL> <MSG>Le message</MSG> </SMS> <SMS> <ID2>56c40c58-8eef-11e6-b3c7-1231420104aa</ID2> <TEL>xxxxxx</TEL> <MSG>Le message</MSG> </SMS>
aujourd'hui je fais comme ça.
Code:

1 2 3 4 5 select t.x.value('ID2[1]','varchar(50)') as ID2, t.x.value('TEL[1]','varchar(50)') as TEL, t.x.value('MSG[1]','varchar(max)') as MSG from @INFOS_XML.nodes('/SMS') t(x)
mais comme on peut le voir je suis obligé de préciser mes noeuds en MAJ
j'ai essayé en minuscule comme ça par exemple t.x.value('id2[1]','varchar(50)') as ID2mais du coup il retrouve rien bien évidemment, comment faire pour ne pas tenir compte de la casse sur les noeuds ?

je ne peux pas passer tout le flux en min ou maj car la casse du message ou de l'id ne doit pas être perdue.

Merci de vos idées.
cH.

03/11/2016, 17h17
SQLpro

Le langage XML est un langage sensible à la casse. Une balise en majuscule n'est pas la même qu'une balise en minuscule. Si tel est le cas de vos document, ceux qui vous les envoient ne respectent pas leurs propres référentiel.... Réglez le problème en amont, ou écrivez autant de requête que de casse ou encore, utilisez les fonction UPPER / LOWER.

A +
03/11/2016, 20h16
Troyan

Bonjour Fred,

Merci pour ces infos, toutefois comme je l'ai dit je ne maîtrise pas les sources et elles sont multiples, j'ai volontairement simplifié l'exemple, de plus certains développeurs ne font plus partis de la boite.
Je pense que vu votre parcours vous avez déjà rencontré ça en entreprise.

J'ai bien pensé à gérer la casse sur l'ensemble du flux (variable) mais du coup ça joue sur les valeurs, alors que ma recherche se porte plutôt sur les nœuds eux mêmes.
J'avoue que je ne connait pas assez le XML et toutes ses possibilités dans SQL...

Je suis preneur d'idées...
cH.
04/11/2016, 08h28
François DORIN

Bonjour,

Effectivement, le XML est sensible à la casse. Ce n'est donc pas SQLServer qui va vous aider à ce niveau là. Il faut traiter le flux avant de l'envoyer en traitement.

Comme le flux est traité par SQLServer, je suppose que tu envoies tout d'un coup. Aussi, avec 2 expressions régulières bien choisie (une pour les tags ouvrant, l'autre pour les tags fermant), il sera possible de ne mettre à jour que les tags, et pas l'intégralité du flux.

Ou sinon il y a la possibilité de parser le document XML, de mettre en majuscule tous les tags avant d'écrire un nouveau document XML et d'envoyer ce nouveau document vers SQLServer. Mais je pense que ce sera plus lourd à mettre en oeuvre que de simples regex.
04/11/2016, 08h52
Troyan

Oui je pensais partir par là en dernière option... :/
Comme j'ai vu que dans les fonction XML de sql y'avait fn:lower-case, etc... je me suis dit qu'il y avait peut être un moyen de modifier les noeuds.
je crains un peu la lourdeur mais je risque de ne pas avoir trop le choix, même si effectivement la logique voudrait de changer tout ça en amont.

Merci en tout cas je vais voir si je trouve un truc côté expressions régulières, qui pourrait me servir.

Citation:

Envoyé par Troyan

Comme j'ai vu que dans les fonction XML de sql y'avait fn:lower-case

Ces fonctions sont faites pour agir sur les données, pas sur la structure du XML. J'ai bien peur qu'elles ne soient pas d'une grande utilité ici.

Maintenant, après de longues réflexions, il est peut-être possible de les utiliser, mais :

cela va rendre la requête difficile lisible ;
cela va nuire grandement aux performances.

En XPATH, tu peux récupérer le nom d'un tag via name(). Tu peux donc appliquer la fonction fn:lower-case.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 DECLARE @INFOS_XML XML = N'<Sms> <ID2>48c40c58-8eef-11e6-b3c7-1231420104ed</ID2> <TEL>xxxxxx</TEL> <MSG>Le message</MSG> </Sms> <SMS> <ID2>56c40c58-8eef-11e6-b3c7-1231420104aa</ID2> <TEL>xxxxxx</TEL> <MSG>Le message</MSG> </SMS>'; select t.x.value('ID2[1]','varchar(50)') as ID2, t.x.value('TEL[1]','varchar(50)') as TEL, t.x.value('MSG[1]','varchar(max)') as MSG from @INFOS_XML.nodes('/*[fn:lower-case(local-name())="sms"]') t(x)

04/11/2016, 09h57
Troyan

Merci dorinf,

c'est exactement ça, je verrais si je peux faire quelques tests avant afin de rentrer dans cette branche vs l'original.
mais dans ton exemple cela se comporte très bien, sur le pire volume que j'ai c'est plutot une bonne chose :)

Si je peux abusé je n'arrive pas a recopier ta fonction au niveau du select sur les 3 balises ID2, TEL, MSG
as tu moyen de me donner un exemple ?
ou de la doc pour savoir comment l'ecrire ?

c'est clair que le XML n'est pas des plus intuitif :)
04/11/2016, 10h26
SQLpro

Le mieux serait quand même de rectifier les doc XML avant intégration via SSIS par exemple.

A +
04/11/2016, 10h41
Troyan

en fait c'est une procédure stockée elle est appelée directement via web , api, exe ...
c'est pour ça, que je ne maitrise pas la source, et c'est pas des fichiers sinon j'aurais pu travaillé directement dessus.

Le risque est faible en vrai, mais il suffit que ça tombe sur le cas "touchy", je préfère être pro-actif quand je vois le futur problème arriver.
d'ou ma question sur le forum parce que je ne voyait pas vraiment comment faire sans que ça soit "crade".

cH.
04/11/2016, 11h35
François DORIN

Citation:

Envoyé par Troyan

en fait c'est une procédure stockée elle est appelée directement via web , api, exe ...
c'est pour ça, que je ne maitrise pas la source, et c'est pas des fichiers sinon j'aurais pu travaillé directement dessus.

Tu peux définir un schéma XML et typer ton paramètre XML à l'aide de ce schéma. En gros, cela veut dire que ta procédure stockée n'acceptera que des données XML respectant le schéma. Si ce n'est pas le cas, la procédure ne sera pas exécutée et une erreur sera générée.
Cela forcera les utilisateurs a utiliser correctement ta procédure.

Je vois le principe mais ça passera jamais ça va me revenir en boomerang direct.

mais ce que tu m'as donné tout à l'heure a l'air de bien se présenter
Code:

1 2 3 4 5 6 select t.x.value('ID2[1]','varchar(50)') as ID2, t.x.value('TEL[1]','varchar(50)') as TEL, t.x.value('MSG[1]','varchar(max)') as MSG from @INFOS_XML.nodes('/*[fn:lower-case(local-name())="sms"]') t(x)
sais tu comment je peux ecrite la partie en rouge dans le select pour appliquer la même chose sur ID2, TEL, MSG ??

merci par avance
cH.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE @INFOS_XML XML = N'<Sms>
  <Id2>48c40c58-8eef-11e6-b3c7-1231420104ed</Id2>
  <TEL>xxxxxx</TEL>
  <MSG>Le message</MSG>
</Sms>
<SMS>
  <ID2>56c40c58-8eef-11e6-b3c7-1231420104aa</ID2>
  <TEL>xxxxxx</TEL>
  <MSG>Le message</MSG>
</SMS>';
 
select
		t.x.value('(*[fn:lower-case(local-name())="id2"]/text())[1]','varchar(50)') as ID2,
		t.x.value('TEL[1]','varchar(50)') as TEL,
		t.x.value('MSG[1]','varchar(max)') as MSG
	from @INFOS_XML.nodes('/*[fn:lower-case(local-name())="sms"]') t(x)

Je l'ai fait pour un élément. Je te laisse faire les autres ;)

04/11/2016, 14h02
Troyan

Génial Merci.
Je pense que je vais pouvoir m'en sortir. :)