Je me débats avec une analyse XML que j'ai. J'ai besoin que les valeurs d'en-tête doivent être des colonnes et que les valeurs d'enregistrement sont les données à l'intérieur de leurs lignes respectives. Vous trouverez ci-dessous un échantillon d'un retour avec les valeurs d'en-tête et 1 enregistrement.
Où enregistre XSI: nil = "vrai" serait null
3 Réponses :
En supposant que vous avez vos données XML dans une variable SQL Server Ceci est assez simple, car vous pouvez supposer que chaque en-tête est vraiment une chaîne (par conséquent, vous pouvez faire le Cependant, pour les données - comme @Sserg déjà mentionné dans le commentaire - à moins que vous ne puissiez en quelque sorte savoir (ou savoir) quelles données Les types de données des éléments sont, cela va être plus délicat ... en utilisant la même approche - en supposant que tout est une chaîne - fonctionnerait - mais vous risquez de perdre des informations précieuses sur vos bits de données: @xmldata , vous pouvez utiliser cette xquery pour obtenir les noms de colonne ("en-têtes"): .value ('(.) [1]', 'varchar (50)') appel et être du côté sûr).
declare @x xml = N'
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header/>
<env:Body>
<ns2:getReportResultResponse xmlns:ns2="http://service.apiendpoint.com">
<return>
<header>
<values>
<data>CUSTOMER NAME</data>
<data>DISPOSITION GROUP A</data>
<data>DISPOSITION GROUP B</data>
<data>DISPOSITION GROUP C</data>
<data>DISPOSITION PATH</data>
<data>FIRST DISPOSITION</data>
<data>LAST DISPOSITION</data>
<data>LIST NAME</data>
</values>
</header>
<records>
<values>
<data>Mark Smith</data>
<data>12</data>
<data>19</data>
<data>23</data>
<data xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<data xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<data xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<data>Tier 1</data>
</values>
<values>
<data>B</data>
<data>2</data>
<data>22</data>
<data>222</data>
<data xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<data xsi:nil="false" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/><!-- ?? -->
<data xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<data>Tier 2</data>
</values>
</records>
</return>
</ns2:getReportResultResponse>
</env:Body>
</env:Envelope>
';
select @x;
declare @sql nvarchar(max) = N'';
with xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' as env, 'http://service.apiendpoint.com' as ns2)
select
@sql = @sql + ',r.rec.value(''data[' + cast(colid as nvarchar(10)) + '][not(@xsi:nil="true")]'', ''nvarchar(500)'') as ' + colname
from
(
select
quotename(hd.h.value('.', 'sysname')) as colname,
row_number() over(order by hd.h) as colid
from @x.nodes('/env:Envelope/env:Body/ns2:getReportResultResponse/return/header/values/data') as hd(h)
) as src
order by colid;
select @sql = stuff(@sql, 1, 1, N'');
select @sql = N'with xmlnamespaces (''http://schemas.xmlsoap.org/soap/envelope/'' as env, ''http://service.apiendpoint.com'' as ns2, ''http://www.w3.org/2001/XMLSchema-instance'' as xsi)
select
' + @sql + N'
from @x.nodes(''/env:Envelope/env:Body/ns2:getReportResultResponse/return/records/values'') as r(rec)
';
exec sp_executesql @stmt = @sql, @params = N'@x xml', @x = @x;
Bonne réponse, +1 de mon côté!
Voici une autre solution. Il est très proche de la méthode de @ LPTR.
Il utilise xquery et flwor expression pour construire une clause dynamique de la finale Déclaration SQL. SQL
sortie
Votre XML ne contient aucune information de type de données pour les colonnes. Y a-t-il une autre source pour déduire des types de données?
C'est exactement la façon dont la réponse me est donnée de l'API. Je disposerai de la configuration des tables de destination avec les types de données pour INSERT, mais le seul moyen de connaître le type de données est le suivant que le rapport i exécuté via l'API .. Cette réponse rapporte les résultats tels qu'ils viennent
Eh bien, quel est le but de convertir XML à une table SQL? Tout plan pour interroger le type de table de
Sélectionnez Somme ([Groupe de disposition A] .. Groupe par [Nom du client] ?