Importer un fichier XML dans une table SQL Server

Bonjour,
Voici mon fichier XML que j'aimerais importer dans une table SQL server 2008 (si possible en faisant un Create table en fonction des metadata d'entrée). A noter que le script devra être impérativement dans une proc stocké, ce qui limite donc les possibilités (pas de SSIS).
J'ai essayé pleins de trucs avec les BULK mais je ne m'en sors pas car j'ai sans doute trop de lacunes sur les format XML.
C'est notamment au niveau des noeuds que je ne comprends pas. Quels sont les noeuds dans mon cas ?
J'ai bien vu des discussions ici mais cela ne m'a pas bcp aidé.

Merci pour votre aide.

PS : le fichier xml est généré par Cognos.
__________________________________________________

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

<?xml version="1.0" encoding="utf-8" ?> - <dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"> - <!-- <dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd" >     --> - <metadata> <item name="Date observation Fait Personnes Produits format AAAAMMJJ" type="xs:int" precision="1" /> <item name="Nature personne" type="xs:string" length="62" /> <item name="Montant Portefeuille (commercial)" type="xs:decimal" scale="2" precision="38" /> </metadata> - <data> - <row> <value>20181031</value> <value xs:nil="true" /> <value>94117,99</value> </row> - <row> <value>20181031</value> <value>PP</value> <value>55080,63</value> </row> - <row> <value>20181031</value> <value>PM</value> <value>20716,52</value> </row> </data> </dataset>

---

Pour importer un fichier XML dans une table il suffit d'utiliser la fonction table OPENROWSET en mode BULK.

Exemple :

Code:

---

1 2 3 4 5 6

CREATE TABLE T_XML (ID INT IDENTITY, X XML); GO   INSERT INTO T_XML SELECT IMPORT.BulkColumn FROM OPENROWSET(BULK N'D:\email.txt', SINGLE_BLOB) AS IMPORT;

---

Après vous pouvez indexer, parser…

A +

ça j'avais réussi à le faire.

C'est après que je n'y suis pas arrivé car notamment, je n'arrivais pas à déterminer mes noeuds.

Il faut que vous utilisiez les fonctions de XQuery pour en déterminer les noms.

Par exemple avec

Code:

---

SELECT X.query('local-name(...)')

---

A lire :
https://docs.microsoft.com/en-us/sql...ql-server-2017

A +

Je n'y arrive tjs pas mais j'ai compris d'où venait le problème. Il vient de ce qu'il y a en haut du fichier xml, une sorte de cartouche.
Car si je modifie à la main mon fichier xml en supprimant cette partie là, ça fonctionne.
Comment ignorer cette partie ?
J'ai essayé en important le fichier xml dans un nvarchar puis de faire un substring mais le nvarchar(max) a une longueur trop petite par rapport à mon fichier.

merci.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

<?xml version="1.0" encoding="utf-8" ?> - <dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"> - <!-- <dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd" >   --> - <metadata> <item name="Date observation Fait Personnes Produits format AAAAMMJJ" type="xs:int" precision="1" /> <item name="Nature personne" type="xs:string" length="62" /> <item name="Montant Portefeuille (commercial)" type="xs:decimal" scale="2" precision="38" /> </metadata>

---

Effectivement UTF8 (qui est une grosse merde) est inutilisable sous SQL Server. Il faut de l'UTF 16.

Vous devez commencer par encoder correctement le fichier (modifier l'entête ne suffira pas...)

A +

Je n'ai pas le choix de l'encodage source et en plus, ça doit etre un traitemement automatique sinon, j'aurais modifié l'encodage sur un editeur de texte et basta.
Là, le traitement sera récurrent et automatique.

Vous n'avez pas d'autre solution :

• ou bien vous encodez correctement votre fichier XML
• ou bien vous renoncez au traitement automatique

A +

Excusez mon ignorance sur le sujet mais en quoi l'encodage du fichier XML influe sur la façon d'enlever ce bout de code qui ne sert à rien ?

Citation:

---

Envoyé par dosilbr

Excusez mon ignorance sur le sujet mais en quoi l'encodage du fichier XML influe sur la façon d'enlever ce bout de code qui ne sert à rien ?

---

Ce bout code qui selon vous "ne sert à rien" sert à l'encodage des caractères du fichier XML. En son absence les caractères seront interprétés n'importe comment, la correspondances entre code hexadécimal et représentation des caractères ne pouvant plus s'effectuer, notament pour tout ce qui est accents... Aucun SGBD ne pouvant deviner quel est l'encodage d'un fichier, ce bout de code est donc primordial !

A lire sur le sujet : https://en.wikipedia.org/wiki/UTF-8

A +

Cela dit, en enlevant ce "bout de code", j'arrive parfaitement à obtenir ce que je souhaite donc dans mon cas de figure, j'aurais souhaité analysé le XML sans cette partie là.

Le problème n'est pas d'encodage. Plutôt quelques autres. Mais comme vous n'avez jamais montré la requête concrète, je ne peux pas dire exactement ce qu'ils sont. Je peux tout de même deviner très précisément la source de problèmes de

Citation:

---

... je n'arrivais pas à déterminer mes noeuds.

---

et puis

Citation:

---

... si je modifie à la main mon fichier xml en supprimant cette partie là, ça fonctionne.

---

Il est important de noter où vivre les noeuds, leur namespace !

Supposons le x soit le colon du type xml ... ceci donnerait suffisamment d'éléments pour vous résoudre le problème vous-même et lire la documentation de xquery avec plus de compréhension.

Code:

---

1 2 3 4 5 6 7 8

select x.query(' declare default element namespace "http://developer.cognos.com/schemas/xmldata/1/"; <racine>{ for $v in /dataset/data/row/value return <valeur>{data($v)}</valeur> }</racine> ') from votreTable

---

Elle résulterait quelque chose comme ça.

Code:

---

1 2 3 4 5 6 7 8 9 10 11

<racine xmlns="http://developer.cognos.com/schemas/xmldata/1/"> <valeur>20181031</valeur> <valeur /> <valeur>94117,99</valeur> <valeur>20181031</valeur> <valeur>PP</valeur> <valeur>55080,63</valeur> <valeur>20181031</valeur> <valeur>PM</valeur> <valeur>20716,52</valeur> </racine>

---

On avance, on avance, merci beaucoup !
C'est presque ça.

En fait (je le répète je ne connais pas le xml) mais il me faudrait qq chose comme (voir ci dessous)

Code:

---

1 2 3 4 5 6 7 8

<row> <value>PP</value> <value>1245,45</value> </row> <row> <value>PM</value> <value>2358</value> </row>

---

pour me mettre d'avoir n enregistrements. Là, dans l'exemple que tu as construit, on a que des value.

Mon objectif, ensuite, à partir de ce xml reconstitué est de le lire via une requête SQL
Exemple :
SELECT
p.value('(./value)[1]', 'nvarchar(max)') as Champ1,
p.value('(./value)[2]', 'nvarchar(max)') as Champ2
FROM dbo.AXMLTable b
CROSS APPLY b.xCol.nodes('/row') t(p);


Par contre, je pense qu'il faudra rajouter un : ;WITH XMLNAMESPACES '......' ??


En tous cas, je te remercie de ton aide.

Si on gardait le parallélisme avec le démo précédant, on pourrait le faire comme ça.

Code:

---

1 2 3 4 5 6 7 8 9 10 11

select x.query(' declare default element namespace "http://developer.cognos.com/schemas/xmldata/1/"; for $i in /dataset/data/row where data($i/value[2]) != "" return <row>{ for $j in $i/value[position()!=1] return $j }</row> ') from votreTable

---

ça ne fonctionne pas mais j'ai fait comme ça et ça "fonctionne" (disons que j'ai un xml qui me semble bien structuré)

Code:

---

1 2 3 4

declare namespace p ="http://developer.cognos.com/schemas/xmldata/1/"; declare default element namespace "uri:SomeNamespace";<Result> { /p:dataset/p:data } </Result>

---

Mais maintenant, je n'arrive pas à constuire ma requête SQL pour avoir une "table" normale.
En fait, je crois qu'il faut déclarer avec WITH XMLNAMESPACES mais cette requête ne me ramène aucune ligne.

Code:

---

1 2 3 4 5 6

;WITH XMLNAMESPACES('http://developer.cognos.com/schemas/xmldata/1/' AS ns) SELECT p.value('(./value)[1]', 'nvarchar(max)') as FirstName, p.value('(./value)[2]', 'nvarchar(max)') as FtName FROM dbo.AXMLTable b CROSS APPLY b.xCol.nodes('ns:row') t(p);

---

C'est bon !!!!

Code:

---

1 2 3 4 5 6 7

;WITH XMLNAMESPACES(DEFAULT 'http://developer.cognos.com/schemas/xmldata/1/') SELECT [ReportId] = p.value('(./value)[1]', 'varchar(40)') , [DocId] = p.value('(./value)[2]', 'varchar(40)')   FROM dbo.AXMLTable b CROSS APPLY b.xCol.nodes('//row') t(p);

---

Merci bcp.