artiso Blog - XML-Felder in T

Sunday, October 05, 2008

XML-Felder in T_SQL abfragen by Thomas Schissler

In einem früheren Beitrag habe ich beschrieben, wie man kleinere Listen mit LINQ effizien speichern kann. Hierzu verwende ich einfach ein XML-Feld in der entsprechenden Tabelle und lege die Liste dann dort als XML-Serialisierung ab. Nun stand ich vor der Herausforderung, dass ich direkt auf der Datenbank dieses Feld abfragen wollte. Glücklicherweise beitet T-SQL eine sehr gute XML-Unterstützung, unglücklicherweise gibt es eine vielzahl von Techniken dies zu tun und die Treffer meiner Internetrecherche waren sehr unübersichtlich. Deshalb hier nochmals kurz zusammengefasst, wie ich das gelöst habe.

Ich habe in einem Feld folgenden XML-Inhalt der ein Dictionary mit 5 Einträgen repräsentiert:

<Root>
  <item>
    <key>
      <int>1</int>
    </key>
    <value>
      <decimal>17.00</decimal>
    </value>
  </item>
  <item>
    <key>
      <int>2</int>
    </key>
    <value>
      <decimal>17.81</decimal>
    </value>
  </item>
  <item>
    <key>
      <int>3</int>
    </key>
    <value>
      <decimal>18.62</decimal>
    </value>
  </item>
  <item>
    <key>
      <int>4</int>
    </key>
    <value>
      <decimal>19.43</decimal>
    </value>
  </item>
  <item>
    <key>
      <int>5</int>
    </key>
    <value>
      <decimal>20.24</decimal>
    </value>
  </item>
</Root>

Die Struktur der XML-Daten ist bei allen Datensätzen gleich. Nun wollte ich diese Werte in 5 separaten Spalten ausgeben um diese einfachin Excel weiterverarbeiten zu können. Das macht folgendes Skript:

SELECT   PackSIzeID, Units,
    u1.l.value('decimal[1]','Decimal(18,2)') AS UnitsYear1,
    u1.l.value('decimal[1]','Decimal(18,2)') AS UnitsYear2,
    u1.l.value('decimal[1]','Decimal(18,2)') AS UnitsYear3,
    u1.l.value('decimal[1]','Decimal(18,2)') AS UnitsYear4,
    u1.l.value('decimal[1]','Decimal(18,2)') AS UnitsYear5
FROM  data_PackSizes 
CROSS APPLY Units.nodes('//Root/item[1]/value') u1(l)
CROSS APPLY Units.nodes('//Root/item[2]/value') u2(l)
CROSS APPLY Units.nodes('//Root/item[3]/value') u3(l)
CROSS APPLY Units.nodes('//Root/item[4]/value') u4(l)
CROSS APPLY Units.nodes('//Root/item[5]/value') u5(l)

Hier kann ich die 5 values als separate Nodes adressieren und dann in der Feldliste entsprechend auswählen. Das Ergebnis sieht dann so aus:

Vielleicht geht das auch noch eleganter, aber für mich hat's erst mal funktioniert und deshalb habe ich hier erst mal nicht mehr Zeit investiert.

Weitere Infos zum Thema:

SQL XML-Workshop von Jacob Sebastian

Technet-Whitepapaer: XML Best Practices for Microsoft SQL Server 2005

Sunday, October 05, 2008 10:29:54 AM (Mitteleuropäische Zeit, UTC+01:00)

Comments [0] SQL |

All comments require the approval of the site owner before being displayed.

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):