webentwicklung-frage-antwort-db.com.de

Extrahieren von Attributen aus XML-Feldern in der SQL Server 2008-Tabelle

Ich habe eine Tabelle mit mehreren Spalten, von denen eine eine xml -Spalte ist. Ich habe keinen Namespace für die Abfrage. Die XML-Daten sind für alle Datensätze immer gleich aufgebaut.

Erfundene Daten

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

Gewünschten Erfolge

Name    Info.x   Info.y   Info
-----   -------  -------  -------
one       42       99     Red
two       27       72     Blue
three     16       51     Green
four      12       37     Yellow

Teilweise funktioniert

select Name, xml_data.query('/data/info/.').value('.', 'varchar(10)') as [Info]
from   #temp

Es werden die Spalten Name und Info zurückgegeben. Ich kann nicht herausfinden, wie man die Attributwerte extrahiert, ohne einen Namespace zu verwenden. Die folgenden Abfragen geben beispielsweise Fehler zurück:

Abfrage 1

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

Abfrage 2

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

Abfrage 3

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

Frage

Wie schreibe ich eine Abfrage, um reguläre Spaltendaten und Element- + Attributwerte aus einer xml -Spalte in derselben Tabelle zurückzugeben?

28
James L.

Kurz nachdem ich die Frage gestellt hatte, stolperte ich über diese Antwort . Ich weiß nicht, warum ich es bei früheren Suchen nicht gefunden habe. Das war die Antwort, nach der ich gesucht habe. Hier ist die Abfrage, die funktioniert:

Abfrage

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

Ergebnis

Name     Info.x    Info.y    Info
-------  --------  --------  ---------
one         42        99     Red
two         27        72     Blue
three       16        51     Green
four        12        37     Yellow

.

------ Bearbeiten [29.01.2014] ------

Ich fand einen anderen Fall, der es wert ist, zu dieser Antwort hinzugefügt zu werden. Mehrere gegeben <info> Elemente innerhalb des <data> Element, es ist möglich, alle <info> Knoten mit 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

In diesem Beispiel wird das folgende Dataset zurückgegeben:

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
37
James L.