MS SQL Server
  1. MS SQL Server
  3. Importer un file XML structuré dans une table SQL
Intereting Posts
Procédure stockée pour une requête dynamic dans SQL Server Comment créer une contrainte de vérification qui assure un logging de détail actif pour une table? Script T-SQL pour supprimer toutes les relations entre un set de tables dans un schéma et un autre set dans un autre schéma? Sql-server Full Text CONTAINS + COLLATE pour ignorer les problèmes d'accents Suivi des modifications apscopes à SQL Server dans l'application C # ou SQL Server Vues partitionnées et locking SQL Server Débordement arithmétique avec bigints dans SQL Server 2008 SQL – données similaires dans la colonne Appelez Fonction seulement une seule fois pour plusieurs endroits dans une requête Snippets et raccourcis SSMS Java n'exécute pas d'instruction préparée avec paramètre et ne génère aucune erreur SQL Server – Comment insert des résultats de sélection de tables différentes dans des sous-requêtes ou des variables de table dans une nouvelle variable de table, dans une procédure stockée? Sélection des loggings DISTINCT sur un sous-set de colonnes avec MAX dans une autre colonne Création d'un model de entity framework couvrant plusieurs bases de données Comment puis-je générer une exception personnalisée dans SQL Server

Importer un file XML structuré dans une table SQL

J'essaie de download et d'importer un file XML dans une table SQL. Le téléchargement fonctionne parfaitement et génère le file XML suivant.

GeoPers.xml 

<?xml version="1.0" encoding="UTF-8"?> <soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <Clocking_GetByDateRangeUtcResponse xmlns="http://www.geodynamics.be/webservices"> <Clocking_GetByDateRangeUtcResult> <ClockingEntity> <Id>af261a77-a35b-4778-a175-6fd50d00cde1</Id> <DateTimeUtc>2017-09-28T02:37:56Z</DateTimeUtc> <DateTimeLocal>2017-09-28T04:37:56</DateTimeLocal> <User> <Id>4975535a-2690-4492-bc7f-5e8a7d839cb9</Id> <Name>Marcel De Doncker</Name> <DayProgramId xsi:nil="true"/> </User> <Vehicle> <Id>52e01d9c-96fa-46ed-98fe-f62e1792f019</Id> <Code>7080</Code> <Name>Auto: Dacia Dokker 1-PAE-120</Name> <VehicleTypeId xsi:nil="true"/> <LastSyncDateUtc xsi:nil="true"/> </Vehicle> <Type>StartMovementDriver</Type> <Location> <Address> <Street>Kapelleveld</Street> <HouseNumber>49</HouseNumber> <PostalCode>1785</PostalCode> <City>Merchtem</City> <Submunicipality>Merchtem</Submunicipality> <Country>Belgium</Country> </Address> <Longitude>4.2380828857421875</Longitude> <Latitude>50.9540901184082</Latitude> </Location> <Pois/> <IsManual>false</IsManual> </ClockingEntity> </Clocking_GetByDateRangeUtcResult> </Clocking_GetByDateRangeUtcResponse> </soap:Body> </soap:Envelope>

J'utilise la requête suivante pour charger le file dans SQL:

SQL QUERY 

 DECLARE @XmlFile XML SELECT @XmlFile = BulkColumn FROM OPENROWSET(BULK 'C:\Temp\GeoDynamics\Downloads\GeoPers.xml', SINGLE_BLOB) x SELECT Id = resource.value('(Id)[1]', 'varchar(255)'), DateTimeUtc = resource.value('(DateTimeUtc)[2]', 'varchar(255)'), DateTimeLocal = resource.value('(DateTimeLocal)[3]', 'varchar(255)'), UserID = resource.value('(User/Id)[4]', 'varchar(255)'), UserName = resource.value('(User/Name)[5]', 'varchar(255)'), VehicleID = resource.value('(Vehicle/Id)[6]', 'varchar(255)'), VehicleCode = resource.value('(Vehicle/Code)[7]', 'varchar(255)'), VehicleName = resource.value('(Vehicle/Name)[8]', 'varchar(255)') FROM @XmlFile.nodes('//Clocking_GetByDateRangeUtcResponse/Clocking_GetByDateRangeUtcResult/ClockingEntity') AS XTbl1(resource)

La requête ne donne aucun résultat, pas même un code d'erreur. Je ne sais pas où je me trompe dans la requête.

Le problème principal est que votre XML a un espace de nom par défaut ( http://www.geodynamics.be/webservices ). Vous devrez utiliser la directive WITH XMLNAMESPACES pour spécifier cet espace de noms pour votre requête.

Comme mentionné dans l'autre réponse, vous devrez également corriger les index dans vos requêtes XPath – ils devraient tous être [1] . 

 WITH XMLNAMESPACES (DEFAULT 'http://www.geodynamics.be/webservices') SELECT Id = resource.value('(Id)[1]', 'varchar(255)'), DateTimeUtc = resource.value('(DateTimeUtc)[1]', 'varchar(255)'), DateTimeLocal = resource.value('(DateTimeLocal)[1]', 'varchar(255)'), UserID = resource.value('(User/Id)[1]', 'varchar(255)'), UserName = resource.value('(User/Name)[1]', 'varchar(255)'), VehicleID = resource.value('(Vehicle/Id)[1]', 'varchar(255)'), VehicleCode = resource.value('(Vehicle/Code)[1]', 'varchar(255)'), VehicleName = resource.value('(Vehicle/Name)[1]', 'varchar(255)') FROM @XmlFile.nodes('//Clocking_GetByDateRangeUtcResponse/Clocking_GetByDateRangeUtcResult/ClockingEntity') AS XTbl1(resource);

Sortie: 

 Id af261a77-a35b-4778-a175-6fd50d00cde1 DateTimeUtc 2017-09-28T02:37:56Z DateTimeLocal 2017-09-28T04:37:56 UserID 4975535a-2690-4492-bc7f-5e8a7d839cb9 UserName Marcel De Doncker VehicleID 52e01d9c-96fa-46ed-98fe-f62e1792f019 VehicleCode 7080 VehicleName Auto: Dacia Dokker 1-PAE-120

Ajouter des namespaces aux requêtes avec WITH XMLNAMESPACES

Cela fonctionnera. Vous ne select pas dans l'espace de noms correct; utilisez WITH XMLNAMESPACES pour cela. Deuxièmement, vous select avec le mauvais index en dehors de l'élément Id ; l'index [n] vous spécifiez sélectionnera la n-ème occurrence de l'élément. Vous devez toujours en premier. 

 DECLARE @x XML=N' <soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <Clocking_GetByDateRangeUtcResponse xmlns="http://www.geodynamics.be/webservices"> <Clocking_GetByDateRangeUtcResult> <ClockingEntity> <Id>af261a77-a35b-4778-a175-6fd50d00cde1</Id> <DateTimeUtc>2017-09-28T02:37:56Z</DateTimeUtc> <DateTimeLocal>2017-09-28T04:37:56</DateTimeLocal> <User> <Id>4975535a-2690-4492-bc7f-5e8a7d839cb9</Id> <Name>Marcel De Doncker</Name> <DayProgramId xsi:nil="true"/> </User> <Vehicle> <Id>52e01d9c-96fa-46ed-98fe-f62e1792f019</Id> <Code>7080</Code> <Name>Auto: Dacia Dokker 1-PAE-120</Name> <VehicleTypeId xsi:nil="true"/> <LastSyncDateUtc xsi:nil="true"/> </Vehicle> <Type>StartMovementDriver</Type> <Location> <Address> <Street>Kapelleveld</Street> <HouseNumber>49</HouseNumber> <PostalCode>1785</PostalCode> <City>Merchtem</City> <Submunicipality>Merchtem</Submunicipality> <Country>Belgium</Country> </Address> <Longitude>4.2380828857421875</Longitude> <Latitude>50.9540901184082</Latitude> </Location> <Pois/> <IsManual>false</IsManual> </ClockingEntity> </Clocking_GetByDateRangeUtcResult> </Clocking_GetByDateRangeUtcResponse> </soap:Body> </soap:Envelope>'; 
 WITH XMLNAMESPACES(DEFAULT 'http://www.geodynamics.be/webservices' ) SELECT Id = resource.value('(Id)[1]', 'varchar(255)'), DateTimeUtc = resource.value('(DateTimeUtc)[1]', 'varchar(255)'), DateTimeLocal = resource.value('(DateTimeLocal)[1]', 'varchar(255)'), UserID = resource.value('(User/Id)[1]', 'varchar(255)'), UserName = resource.value('(User/Name)[1]', 'varchar(255)'), VehicleID = resource.value('(Vehicle/Id)[1]', 'varchar(255)'), VehicleCode = resource.value('(Vehicle/Code)[1]', 'varchar(255)'), VehicleName = resource.value('(Vehicle/Name)[1]', 'varchar(255)') FROM @x.nodes('//Clocking_GetByDateRangeUtcResponse/Clocking_GetByDateRangeUtcResult/ClockingEntity') AS XTbl1(resource)