Extraire des attributs des Champs XML dans la Table SQL Server 2008
j'ai un tableau avec plusieurs colonnes, dont l'une est une colonne xml . Je n'ai pas de noms à utiliser dans la requête. Les données XML sont toujours la même structure pour tous les enregistrements.
Artificiel "Données De 1519130920"
create table #temp (id int, name varchar(32), xml_data xml)
insert into #temp values
(1, 'one', '<data><info x="42" y="99">Red</info></data>'),
(2, 'two', '<data><info x="27" y="72">Blue</info></data>'),
(3, 'three', '<data><info x="16" y="51">Green</info></data>'),
(4, 'four', '<data><info x="12" y="37">Yellow</info></data>')
Résultats Souhaités
Name Info.x Info.y Info
----- ------- ------- -------
one 42 99 Red
two 27 72 Blue
three 16 51 Green
four 12 37 Yellow
Partiellement Œuvres
select Name, xml_data.query('/data/info/.').value('.', 'varchar(10)') as [Info]
from #temp
create table #temp (id int, name varchar(32), xml_data xml)
insert into #temp values
(1, 'one', '<data><info x="42" y="99">Red</info></data>'),
(2, 'two', '<data><info x="27" y="72">Blue</info></data>'),
(3, 'three', '<data><info x="16" y="51">Green</info></data>'),
(4, 'four', '<data><info x="12" y="37">Yellow</info></data>')
Name Info.x Info.y Info
----- ------- ------- -------
one 42 99 Red
two 27 72 Blue
three 16 51 Green
four 12 37 Yellow
select Name, xml_data.query('/data/info/.').value('.', 'varchar(10)') as [Info]
from #temp
il renvoie les colonnes Name et Info . Je ne peux pas comprendre comment extraire les valeurs des attributs sans utiliser d'espace de noms. Par exemple, les requêtes suivantes renvoient des erreurs:
select Name, xml_data.query('/data/info/@x') as [Info]
from #temp
Msg 2396, Level 16, State 1, Line 12
XQuery [#temp.xml_data.query()]: Attribute may not appear outside of an element
select Name, xml_data.value('/data/info/@x', 'int') as [Info]
from #temp
Msg 2389, Level 16, State 1, Line 12
XQuery [#temp.xml_data.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
select Name, xml_data.query('/data/info/.').value('@x', 'int') as [Info]
from #temp
Msg 2390, Level 16, State 1, Line 9
XQuery [value()]: Top-level attribute nodes are not supported
Question
Comment écrire une requête pour retourner des données de colonne régulières, et des valeurs d'attribut element + d'une colonne xml dans la même table?
1 réponses
juste après avoir posté la question, je suis tombé sur cette réponse . Je ne sais pas pourquoi je ne l'ai pas trouvé dans les recherches précédentes. C'était la réponse que je cherchais. Voici la requête qui fonctionne:
Requête
select Name
,xml_data.value('(/data/info/@x)[1]', 'int') as [Info.x]
,xml_data.value('(/data/info/@y)[1]', 'int') as [Info.y]
,xml_data.value('(/data/info/.)[1]', 'varchar(10)') as [Info]
from #temp
résultat
Name Info.x Info.y Info
------- -------- -------- ---------
one 42 99 Red
two 27 72 Blue
three 16 51 Green
four 12 37 Yellow
.
------ Modifier [2014-01-29] ------
j'ai trouvé un autre cas qui vaut la peine d'ajouter à ce réponse. Étant donné les multiples éléments <info> dans l'élément <data> , il est possible de retourner tous les noeuds <info> en utilisant cross apply :
create table #temp (id int, name varchar(32), xml_data xml)
insert into #temp values
(1, 'one', '<data><info x="42" y="99">Red</info><info x="43" y="100">Pink</info></data>'),
(2, 'two', '<data><info x="27" y="72">Blue</info><info x="28" y="73">Light Blue</info></data>'),
(3, 'three', '<data><info x="16" y="51">Green</info><info x="17" y="52">Orange</info></data>'),
(4, 'four', '<data><info x="12" y="37">Yellow</info><info x="13" y="38">Purple</info></data>')
select Name
,C.value('@x', 'int') as [Info.x]
,C.value('@y', 'int') as [Info.y]
,C.value('.', 'varchar(10)') as [Info]
from #temp cross apply
#temp.xml_data.nodes('data/info') as X(C)
drop table #temp
cet exemple retourne l'ensemble de données suivant:
Name Info.x Info.y Info
--------- ----------- ----------- ----------
one 42 99 Red
one 43 100 Pink
two 27 72 Blue
two 28 73 Light Blue
three 16 51 Green
three 17 52 Orange
four 12 37 Yellow
four 13 38 Purple