Importer un fichier XML dans une table SQL Server

**dosilbr** · 10/11/2018, 09h52

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

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

**SQLpro** · 10/11/2018, 19h10

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

Exemple :

Code :

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

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 +

**dosilbr** · 11/11/2018, 10h14

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

**SQLpro** · 12/11/2018, 16h43

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

Par exemple avec

Code :

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

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

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

A +

**dosilbr** · 02/12/2018, 19h27

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

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

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>

**SQLpro** · 03/12/2018, 08h52

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 +

**dosilbr** · 03/12/2018, 10h05

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.

**SQLpro** · 03/12/2018, 11h18

Vous n'avez pas d'autre solution :

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

A +

**dosilbr** · 03/12/2018, 12h19

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 ?

**SQLpro** · 03/12/2018, 13h45

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 +

**dosilbr** · 03/12/2018, 14h24

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

**tsuji** · 03/12/2018, 16h34

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

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

et puis

... 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 sql :

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

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

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

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>

**dosilbr** · 03/12/2018, 17h12

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 :

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

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.

**tsuji** · 03/12/2018, 18h07

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

Code :

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

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

**dosilbr** · 03/12/2018, 18h31

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

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

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 :

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

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

**dosilbr** · 03/12/2018, 18h38

C'est bon !!!!

Code :

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

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.

Importer un fichier XML dans une table SQL Server

Développement SQL Server

Discussions similaires

Partager

Partager