11.5.2 Ergebnis wird im XML-Format zurückgegeben
Werbung
440 Datenaustausch Bild 11.11: Über HTTP eine Abfrage in der MS SQL Server-Datenbank ausführen 11.5.2 Ergebnis wird im XML-Format zurückgegeben Die SELECT-Anweisung kann beim MS SQL Server 2000 eine neue Klausel enthalten. Mit der Klausel „FOR XML“ werden die Daten im XML-Format und nicht als gewohntes Rowset zurückgegeben. Dabei existieren drei Möglichkeiten, die einen unterschiedlichen Grad an Flexibilität für die Definition des XML-Ergebnisses festlegen, das Sie von der Abfrage erhalten. • Raw • Auto • Explicit Die SQL-Erweiterung für die „FOR XML“-Klausel sieht folgendermaßen aus: SELECT… FROM… WHERE… ORDER BY… FOR XML (raw | auto [, ELEMENTS] | explicit) „Raw“-Modus Die erste Möglichkeit, der „Raw“-Modus, ist ein einfacher Modus. Er nimmt eigentlich nur das Ergebnis der Abfrage und definiert, dass jede Zeile ein XML-Zeilenelement wird. Es gibt keine Verschachtelung, da der MS SQL Server keine geschachtelten Tabellen kennt. Die MS SQL Server und XML 441 einzelnen Spalten und deren Werte im Abfrageergebnis werden in XML als AttributWertpaare in der Zeile abgebildet. Der Attributname bildet sich dabei aus dem Spaltennamen. Der Aufbau des XML-Ergebnisses ähnelt beim „Raw“-Modus dem Aufbau einer CSV-Datei, nur mit dem Unterschied, dass es XML-Format ist. Die nächste Abfrage stellt eine einfache Verknüpfung zwischen den beiden Tabellen „Abteilung“ und „Mitarbeiter“ dar. Es sollen der Abteilungsname und die Nachnamen der einzelnen Mitarbeiter angezeigt werden. SELECT abt_name, Nachname FROM Abteilung, Mitarbeiter WHERE Abteilung.abt_nr = Mitarbeiter.Abteilungs_nr FOR XML raw Diese „SELECT“-Anweisung können Sie im SQL Server Query Analyzer eingeben. Da das Ergebnis in einer Zeile zurückgeliefert wird, ist es sinnvoll, das Resultat nicht anzeigen, sondern in eine Datei speichern zu lassen. In dieser Datei können Sie dann selber Zeilenumbrüche zum besseren Lesen einfügen. Falls Sie, wie oben beschrieben, ein virtuelles Stammverzeichnis bezogen auf die MS SQL Server-Datenbank „Firma“ angelegt haben, können Sie den SQL-Code auch im Browser ausführen. Die vollständige Zeile lautet dafür folgendermaßen: http://Galah/Firma?sql=SELECT abt_name, Nachname FROM Abteilung, Mitarbeiter WHERE Abteilung.abt_nr = Mitarbeiter.Abteilungs_nr FOR XM raw&root=root Das Ergebnis enthält keine strukturellen Informationen, sondern nur für jede existierende Kombination eine Zeile, das heißt ein XML-Element mit dem allgemeinen Bezeichner „row“. Es können keine Rückschlüsse mehr gezogen werden, welche Daten aus welcher Tabelle stammen. <row abt_name="Vertrieb <row abt_name="Marketing <row abt_name="Vertrieb … <row abt_name="Support " Nachname="Keller " Nachname="Huber " Nachname="Müller "/> "/> "/> " Nachname="Müller "/> „Auto“-Modus Falls das Resultat eine Struktur besitzt, aber die Arbeit nicht zu aufwändig werden soll, können Sie den „Auto“-Modus einsetzen. Das Resultat der Abfrage wird in diesem Fall als geschachtelte XML-Elemente geliefert. Zusätzlich werden die Namen der Tabellen beziehungsweise Sichten zur Bildung des Elementnamens verwendet. Die Spaltennamen werden für die Attribute benutzt oder können die Unterelemente repräsentieren. Wenn dieselbe „SELECT“-Anweisung wie beim „Raw“-Modus verwendet wird, sieht das Ergebnis schon ein wenig anprechender aus. Da der Tabellenname mit übernommen wird, weiß man sofort, aus welcher Tabelle die einzelnen Daten stammen. 442 Datenaustausch Für die Abbildung wurde folgende Zeile in das Kombinationsfeld „Adresse“ des Internet Explorers eingegeben. Dies funktioniert nur, wenn das virtuelle Stammverzeichnis „Firma“ existiert. http://Galah/Firma?sql=SELECT abt_name, Nachname FROM Abteilung, Mitarbeiter WHERE Abteilung.abt_nr = Mitarbeiter.Abteilungs_nr FOR XM auto&root=root Bild 11.12: Die Klausel „FOR XML“ mit dem „Auto“-Modus Erscheinen mehrere Mitarbeiter derselben Abteilung direkt aufeinander folgend, werden sie zu einer Gruppe zusammengefasst, das heißt, die XML-Elemente werden geschachtelt. Die Schachtelung hängt von der Reihenfolge der angegebenen Tabellen ab. Da zuerst die Tabelle „Abteilung“ genannt wurde, stellt der Abteilungsname die oberste Hierarchie dar und kann somit mehrere Mitarbeiter der nächsten Hierarchieebene enthalten. Würden Sie in der „SELECT“-Anweisung die Tabelle „Mitarbeiter“ zuerst und dann die Tabelle „Abteilung“ schreiben, wäre die oberste Hierarchieebene der Nachname und es könnten keine Untereinträge gebildet werden. „Explicit“-Modus Als dritten Modus können Sie bei der „FOR XML“-Klausel den „Explicit“-Modus wählen. Damit legen Sie fest, dass die Struktur des XML-Resultats explizit definiert ist. Diese Definition erfolgt über zusätzliche Angaben zur Schachtelung direkt in der Abfrage. MS SQL Server und XML 443 Für den „Explicit“-Modus ist es notwendig, dass die Abfrage zwei bestimmte Spalten enthält, die als Metadatenspalten bezeichnet werden. • Die erste Spalte muss die Tagnummer des aktuellen Elements mit dem Datentyp „integer“ sein. • Die zweite Spalte muss die Tagnummer des übergeordneten Elements sein. Sie wird deswegen auch als „Parent“-Spalte bezeichnet. Auch hier wird der Datentyp „integer“ benötigt. Der Inhalt dieser beiden Spalten wird zum Erkennen und zur Darstellung der „Parent-Child“Hierarchie benötigt. Nur so kann die gewünschte Struktur im XML-Dokument richtig aufgebaut werden. Wenn in einer „Parent“-Zeile der Wert 0 oder Null steht, handelt es sich bei dem Element um die oberste Hierarchieebene. Es sollen wieder die einzelnen Abteilungen mit den dazugehörigen Mitarbeitern angezeigt werden. Im Gegensatz zu den letzten beiden Beispielen soll nun aber jeder Abteilungsname nur noch einmal erscheinen, und alle Mitarbeiter sollen unter der entsprechenden Abteilung aufgelistet werden. Für die Realisierung werden zwei „SELECT“-Anweisungen benötigt, die über die „UNION ALL“-Anweisung zu einem Ergebnis zusammengefasst werden. Die erste „SELECT“-Anweisung basiert nur auf der Tabelle „Abteilung“ und besteht aus vier Spalten. Da die Tabelle „Abteilung“ in der Hierarchie an der obersten Stelle steht, bekommt jeder Datensatz als „Tag“ den Wert 1 und als „Parent“ den Wert „Null“ übergeben. Die vierte Spalte enthält auch jeweils den Wert „Null“, da sie nur bei der nächsten „SELECT“-Anweisung gefüllt wird. Die zweite „SELECT“-Anweisung ähnelt sehr der Abfrage, die beim „Raw“-Modus und beim „Auto“-Modus verwendet wurde. Der Unterschied liegt nur in den beiden ersten Spalten, die neu hinzugekommen sind. Die Tagnummer lautet für jeden Datensatz 2. Da es sich nun um Sätze der zweiten Hierarchieebene handelt, muss in die „Parent“-Zelle der Wert der übergeordneten Ebene, das heißt 1, geschrieben werden. Die „UNION ALL“-Anweisung wird nach dem Abteilungsnamen und dem Nachname sortiert. Am Ende der gesamten Anweisung wird der „Explicit“-Modus angegeben. SELECT 1 NULL Abteilung.abt_name NULL FROM Abteilung as Tag, as Parent, as [Abteilung!1!abt_name], as [Mitarbeiter!2!Nachname] UNION ALL SELECT 2, 1, Abteilung.abt_name, Mitarbeiter.Nachname FROM Abteilung, Mitarbeiter WHERE Abteilung.abt_nr = Mitarbeiter.Abteilungs_nr ORDER BY [Abteilung!1!abt_name], [Mitarbeiter!2!Nachname] FOR XML EXPLICIT 444 Datenaustausch Das Ergebnis dieser Abfrage wird in der Abbildung gezeigt. Damit die Abfrage im Browser ausgeführt werden kann, muss das virtuelle Stammverzeichnis „Firma“ existieren. Bild 11.13: Die Klausel „FOR XML“ mit dem „Explizit“-Modus Einschränkungen für die „FOR XML“-Klausel Die Verwendung der „FOR XML“-Klausel unterliegt mehreren Einschränkungen. Einige wichtige sind nachfolgend aufgelistet: • Sie kann nicht in untergeordneten „SELECT“-Anweisungen benutzt werden. • Sie kann nicht zusammen mit der Klausel „COMPUTE BY“ eingesetzt werden. • Der „Auto“-Modus wird nicht zusammen mit Aggregatfunktionen und der Klausel „GROUP BY“ unterstützt. • Sie kann nicht in einer Sicht oder einer benutzerdefinierten Funktion, die ein Rowset zurückliefert, zum Einsatz kommen. • Sie kann nicht zusammen mit Cursorn benutzt werden. 11.5.3 XML-Daten in einer Abfrage auswerten Wenn Sie Daten, die im XML-Format vorliegen, in einer Abfrage verwenden wollen, können Sie dazu die neue Transact-SQL-Funktion „OPENXML“ einsetzen. Diese Funktion liefert ähnlich wie eine Tabelle oder eine Sicht eine zweidimensionale Darstellung von Daten, das MS SQL Server und XML 445 heißt, ein Rowset als Ergebnis zurück. Aus diesem Grund wird die Funktion auch als Rowsetprovider bezeichnet. In „SELECT“- und „SELECT INTO“-Anweisungen kann die Funktion „OPENXML“ überall dort stehen, wo sonst Tabellen oder Sichten genannt werden. Die von einem XML-Dokument über die Funktion „OPENXML“ bereitgestellten Datensätze können für die Weiterverarbeitung auch über die „INSERT“-Anweisung in Tabellen der Datenbank gespeichert werden. Hinweis: Da die Funktion „OPENXML“ viele Elemente der XPath-Sprache einsetzt, ist es sehr sinnvoll, auch Wissen in dieser Sprache zu besitzen, wenn Sie mit „OPENXML“ arbeiten. Eine interne Darstellung des XML-Dokuments erstellen Damit in Abfragen die Funktion „OPENXML“ eingesetzt werden kann, muss im ersten Schritt über die gespeicherte Systemprozedur „sp_xml_preparedocument“ eine interne Darstellung des XML-Dokuments angefertigt werden. Dazu analysiert der MSXML-Parser („Msxml2.dll“) den übergegebenen XML-Text und baut intern eine Struktur verschiedener Knoten auf. Bei den Knoten handelt es sich vor allem um Elemente, Attribute und Kommentare. Die Prozedur „sp_xml_preparedocument“ gibt einen Verweis, ein Handle, auf diese interne Struktur zurück, den Sie dann an die Funktion „OPENXML“ weiterreichen. Im nächsten Beispiel wird das Handle in die Variable „iHandleDoc“ gespeichert. Im folgenden Beispiel werden einige Daten zu den beiden Bundesländern Süd- und Westaustralien in einem XML-Dokument gespeichert. Jedes Bundesland besitzt Informationen zu einem bestimmten Gebiet. Jedes Gebiet ist wiederum in Gebietsdetails untergliedert, die verschiedene Orte auflisten und angeben, ob dieser Ort auch über Einkaufsmöglichkeiten verfügt. Der XML-Text wird über die „Set“-Anweisung der Variablen „@XMLDoc“ zugewiesen. DECLARE @iHandleDoc int DECLARE @XMLDoc varchar(1000) SET @XMLDoc =' <ROOT> <Bundesland BL_NR="SA" BL_Name="Suedaustralien"> <Gebiet BL_NR="SA" G_Name="Flinders Range" G_Klima="Trocken"> <Gebietsdetail GebietsID="1" Ort="Hawker" Shop="Ja"/> <Gebietsdetail GebietsID="1" Ort="Blinman" Shop="Nein"/> </Gebiet> </Bundesland> <Bundesland BL_NR="WA" BL_Name="Westaustralien"> <Gebiet BL_NR="WA" G_Name="Hamersley Range" G_Klima="Heiss"> <Gebietsdetail GebietsID="3" Ort="Wittenoom" Shop="Nein"/> <Gebietsdetail GebietsID="3" Ort="Tom Price" Shop="Ja"/> </Gebiet> </Bundesland> </ROOT>' EXEC sp_xml_preparedocument @iHandleDoc OUTPUT, @XMLDoc 446 Datenaustausch Die Funktion „OPENXML“ einsetzen Nach der internen Aufbereitung des XML-Texts kann die Funktion „OPENXML“ zum Beispiel in einer „SELECT“-Anweisung verwendet werden. Die Funktion besitzt die folgende Syntax: OPENXML(idoc int [in], rowpattern nvarchar[in], [flags byte[in]]) [WITH (SchemaDeclaration | TableName)] • „idoc“ : Das Handle auf die interne Darstellung des XML-Dokuments. • „rowpattern“: Ein XPath-Ausdruck, der die Knoten identifiziert, die als Zeilen verarbeitet werden sollen. • „flags“: Bestimmt optional die Zuordnung, die zwischen den XML-Daten und dem Rowset zur Verwendung kommen soll. • „SchemaDeclaration“: Optionale Deklaration des Schemas oder Name der Tabelle, die das gewünschte Schema besitzt. Der Wert im Argument „flag“ bestimmt die Art der Zuordnung. Lautet der Wert 1, handelt es sich um die attributzentrierte Zuordnung. Dies bedeutet, dass die Attribute im XMLDokument den Spalten im Rowset zugeordnet werden. Diese anzuzeigenden Spalten werden beim Einsatz einer Schema-Deklaration mit ihrem Namen und dem Datentyp genannt. Wenn Sie für den Parameter „flag“ den Wert 2 eintragen, legen Sie die elementzentrierte Zuordnung fest. Durch die Zahl 3 im Argument „flag“ werden beide Zuordnungsarten erlaubt. In der nachfolgenden „SELECT“-Anweisung werden die Nummern und Namen der Bundesländer abgefragt. Als erster Parameter wird die Variable „@iHandleDoc“ übergeben, die der Rückgabewert der gespeicherten Systemprozedur „„sp_xml_preparedocument“ war. Im XPath-Ausdruck ist der Knoten „Bundesland“ spezifiziert, der eine Hierarchiestufe unter dem „Root“-Knoten liegt. Es muss dabei immer der komplette Lokalisierungspfad genannt werden. SELECT * FROM OPENXML (@iHandleDoc, '/ROOT/Bundesland',1) WITH (BL_NR varchar(5), BL_Name varchar(25)) Sowohl die Definition und die interne Aufbereitung des XML-Dokuments als auch die „SELECT”-Anweisung kann im SQL Server Query Analyzer ausprobiert werden. Als Ergebnis werden, wie Sie in der Abbildung sehen, die beiden Bundesländer „Südaustralien“ und „Westaustralien“ zurückgeliefert. Dieses Beispiel finden Sie auch auf der CD unter „OPENXML.sql“ gespeichert. MS SQL Server und XML 447 Bild 11.14: Die Funktion „OPENXML“ einsetzen Um alle Orte auszugeben, müssen bei der Funktion „OPENXML“ nur ein anderer XPathAusdruck und eine andere Spalte angegeben werden: SELECT * FROM OPENXML (@iHandleDoc, '/ROOT/Bundesland/Gebiet/Gebietsdetail',1) WITH (Ort varchar(25)) Falls Sie beim Beispiel zur Anzeige der Nummern und Namen der Bundesländer nicht die attribut- sondern die elementzentrierte Zuordnung verwenden, indem Sie für das Argument „flag“ den Wert 2 übergeben, wird nur Null im Ergebnis dargestellt. Dies liegt daran, dass die <Bundesland>-Elemente keine Teilelemente besitzen. Bei den beiden Einträgen „BL_NR“ und „BL_Name“ handelt es sich nämlich nicht um Elemente, sondern um Attribute. Damit bei der elementzentrierten Zuordnung dasselbe Ergebnis wie bei der attributzentrierten Zuordnung geliefert wird, muss das XML-Dokument etwas anders aufgebaut werden. Im folgenden XML-Text sind daher aus den bisherigen Attributen „BL_NR“ und „BL_Name“ Teilelemente vom Element „Bundesland“ geworden. <ROOT> <Bundesland> <BL_NR>SA</BL_NR> <BL_Name>Suedaustralien</BL_Name> <Gebiet BL_NR="SA" G_Name="Flinders Range" G_Klima="Trocken"> <Gebietsdetail GebietsID="1" Ort="Hawker" Shop="Ja"/> <Gebietsdetail GebietsID="1" Ort="Blinman" Shop="Nein"/> </Gebiet> </Bundesland> <Bundesland> 448 Datenaustausch <BL_NR>WA</BL_NR> <BL_Name>Westaustralien</BL_Name> <Gebiet BL_NR="WA" G_Name="Hamersley Range" G_Klima="Heiss"> <Gebietsdetail GebietsID="3" Ort="Wittenoom" Shop="Nein"/> <Gebietsdetail GebietsID="3" Ort="Tom Price" Shop="Ja"/> </Gebiet> </Bundesland> </ROOT>' Die interne Darstellung des XML-Dokuments löschen Wenn die interne Darstellung des XML-Dokuments nicht mehr benötigt wird, sollte sie mit der gespeicherten Systemprozedur „sp_xml_removedocument“ wieder aus dem Speicher entfernt werden. Das Handle kann danach nicht mehr verwendet werden. Es wird auch ungültig, wenn die Verbindung zum MS SQL Server getrennt wird. EXEC sp_xml_removedocument @iHandleDoc 11.5.4 Die Abfragesprache XPath Die Sprache XPath kann sehr gut mit der Abfragesprache SQL verglichen werden, nur mit dem Unterschied, dass XPath für XML-Dokumente und nicht für relationale Tabellen entwickelt wurde. Mit XPath können somit Daten aus einem XML-Dokument ermittelt werden. Genau gesagt handelt es sich bei XPath um eine Abfragesprache für XML, die es erlaubt, verschiedene Knoten in einem XML-Baum unter Berücksichtigung bestimmter Kriterien auszulesen. XPath-Abfragen werden vor allem in URLs, in ADO-API und in XML-Vorlagen eingesetzt. XPath steht als Abkürzung für „XML Path Language“ und wird auch als Graphnavigationssprache bezeichnet, da die Navigation durch die hierarchische Struktur eines XMLDokuments möglich ist. Diese Sprache besteht aus einer Pfad-basierten Syntax, die verwendet wird, um Untermengen aus XML-Datenbäumen abfragen zu können. Ein Ausdruck beschreibt in XPath meistens eine Knotenmenge in einem XML-Baum. Knoten können dabei auf vielfältige Arten angesprochen werden, zum Beispiel durch ihren Typ, ihren Namen oder ihre Beziehung. Die Abfragesprache XPath ist eine wichtige Schicht in der gesamten XML-Technologie. Sie können sich vorstellen, dass XPath zwischen der untersten Ebene XML und den oberen Ebenen liegt, die aus XSLT und XML-Schema gebildet werden. XPath erlaubt Entwicklern, Untermengen von Knoten und Beziehungen zwischen Knoten zu beschreiben, ohne Rücksicht auf die später verwendete Programmiersprache nehmen zu müssen. Der darunter liegende XML-Prozessor übernimmt die Arbeit der internen Aufbereitung. Heutzutage unterstützen bereits zahlreiche XML-Prozessoren die Sprache XPath. Als Beispiele sollen an dieser Stelle der MSXML-Parser der Version 3.0 von Microsoft, der Oracle Java DOM-Parser und der Apache’s Xerces-Parser genannt werden.
