Convertir un champ XML vers un format relationnel

Version imprimable

Bonjour,

Je souhaiterais savoir s'il est possible (et si oui comment ;)) de créer une requête SQL capable de convertir en ensemble de lignes possédant une colonne nvarchar(max) contenant du XML sous cette forme:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 <ArrayOfBedControlResult> <BedControlResult> <Location>S07Robot</Location> <Name>Callage sur cible X1</Name> <MeasureData>0</MeasureData> <Status>1</Status> <IsCorrected>false</IsCorrected> <Notes>Calib_X1 : OK</Notes></BedControlResult> <BedControlResult> <Location>S07Robot</Location> <Name>Callage sur cible X2</Name> <MeasureData>0</MeasureData> <Status>1</Status> <IsCorrected>false</IsCorrected> <Notes>Calib_X2 : OK</Notes></BedControlResult> ... ...</ArrayOfBedControlResult>
vers un format relationnel traditionnel :

Citation:

Location | Name | MeasureData | Status | IsCorrected | Notes
---------------------------------------------------------------
S07Robot | Callage sur cible X1 | 0 | 1 | false | Calib_X1 : OK
S07Robot | Callage sur cible X2 | 0 | 1 | false | Calib_X2 : OK
...
...

Mes connaissances en xpath sont très limitées :oops:... pour l'instant je suis parti sur une requête de ce type pour déjà tester sur un seul champ (Location) mais cela ne retourne rien :
Code:

1 2 3 SELECT Convert(XML,cp.Data).query('for $Location in /root/ArrayOfBedControlResult/BedControlResult/Location return $Location') as Result FROM Core_BaseCustomProperty cp
Si quelqu'un a une idée je suis preneur !

Merci d'avance !

Yann.

Bonjour,

Pour obtenir de bonnes performances, changez le type de votre colonne en XML :

Code:

1
2
ALTER TABLE Core_BaseCustomProperty
ALTER COLUMN Data XML

Vous pouvez de plus valider votre colonne XML à l'aide une schéma XML.
Pour enregistrer votre schéma, utilisez l'instruction CREATE XML SCHEMA COLLECTION.
Une fois cela fait, vous pourrez écrire :

Code:

1
2
ALTER TABLE Core_BaseCustomProperty
ALTER COLUMN Data XML (CONTENT dbo.monSchemaXML)

Vous pouvez en plus ajouter un index XML sur cette colonne à l'aide de l'instruction CREATE XML INDEX ;)

Voici un exemple pour ce que vous demandez :

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
DECLARE @xml XML
SET @xml = '<ArrayOfBedControlResult>
    <BedControlResult>
        <Location>S07Robot</Location>
        <Name>Callage sur cible X1</Name>
        <MeasureData>0</MeasureData>
        <Status>1</Status>
        <IsCorrected>false</IsCorrected>
        <Notes>Calib_X1 : OK</Notes> 
    </BedControlResult>
    <BedControlResult>
        <Location>S07Robot</Location>
        <Name>Callage sur cible X2</Name>
        <MeasureData>0</MeasureData>
        <Status>1</Status>
        <IsCorrected>false</IsCorrected>
        <Notes>Calib_X2 : OK</Notes>
    </BedControlResult>
</ArrayOfBedControlResult>'
 
SELECT BedControlResult.value('./Location[1]', 'VARCHAR(10)') Location,
		BedControlResult.value('./Name[1]', 'VARCHAR(50)') Name,
		BedControlResult.value('./MeasureData[1]', 'INT') MeasureData,
		BedControlResult.value('./Status[1]', 'BIT') [Status],
		BedControlResult.value('./IsCorrected[1]', 'VARCHAR(5)') IsCorrected,
		BedControlResult.value('./Notes[1]', 'VARCHAR(50)') Notes
FROM @xml.nodes('/ArrayOfBedControlResult/BedControlResult') TMP (BedControlResult)

@++ ;)

05/11/2009, 16h02
SQLpro

http://blog.developpez.com/sqlpro/p8...t-xml-simple-/

A +
05/11/2009, 17h09
coryan

Merci mille fois pour la rapidité et la qualité de ces informations qui m'ont permis d'obtenir le résultat souhaité... Je suis impressionné !

Yann.