| Übersicht |
 Alle SeminareInfo-BroschüreAnmeldeformularHotels & AnfahrtAGB |
Comelio GmbH
Essen
Fon: +49(0)201-437517-0
Fax: +49(0)201-437517-10
info@comelio.com
Comelio GmbH
Berlin
Fon: +49(0)30-3640339-80
Fax: +49(0)30-3640339-89
info@comelio.com
Comelio GmbH
Hamburg
Fon: +49(0)40-20934996-0
Fax: +49(0)40-20934996-9
info@comelio.com
Comelio GmbH
Frankfurt
Fon: +49(0)69-17320683-0
Fax: +49(0)69-17320683-9
info@comelio.com
Comelio GmbH
München
Fon: +49(0)89-38156860-0
Fax: +49(0)89-38156860-9
info@comelio.com
Comelio GmbH
Stuttgart
Fon: +49(0)711-46051275-0
Fax: +49(0)711-46051275-9
info@comelio.com
Comelio GmbH
Leipzig
Fon: +49(0)341-3928790-0
Fax: +49(0)341-3928790-9
info@comelio.com
Comelio GmbH
Köln
Fon: +49(0)221-355337943-0
Fax: +49(0)221-355337943-9
info@comelio.com
Comelio GmbH
Düsseldorf
Fon: +49(0)211-63556420-0
Fax: +49(0)211-63556420-9
info@comelio.com
Comelio-Blog > MS SQL Server > XML in T-SQL zerlegen
XML in T-SQL zerlegen
|
|
 | Kontakt |
Neben dem Erzeugen und Speichern von XML, ist es auch wichtig die XML Daten zerlegen zu können. Wenn man bspw. Daten aus einer XML Datei in einer relationale Datenbank importieren und auf verschiedene Tabellen verteilen möchte. Bei einer einzelnen Tabelle ist dies natürlich nicht so schwer, als wenn aus den XML-Daten nur einzelne Teilbäume für tabellenbezogene Ladevorgänge benötigt werden. Die Lösung nennt sich XPath, wobei sich dies als SQL für XML vorstellen.XML zerlegen
Neben dem Erzeugen und Speichern von XML, ist es auch wichtig die XML Daten zerlegen zu können. Wenn man bspw. Daten aus einer XML Datei in einer relationale Datenbank importieren und auf verschiedene Tabellen verteilen möchte. Bei einer einzelnen Tabelle ist dies natürlich nicht so schwer, als wenn aus den XML-Daten nur einzelne Teilbäume für tabellenbezogene Ladevorgänge benötigt werden.
Mit Hilfe der Anweisung OPENXML lässt sich XML zerlegen, und mit der Pfadbeschreibungssprache XPath, die typischerweise in einer vereinfachten Version bspw. in XSLT genutzt werden kann, lassen sich Teilbäume selektieren und sogar Bedingungen anwenden. XPath,kann man sich hier als SQL für XML vorstellen. Vor der Vorstellung des Befehls, welcher breite Nutzungsmöglichkeiten bietet, ist es wichtig zunächst zu sehen, welche Datenstrukturen er liefert, wenn noch gar keine Zerlegung stattfindet. Dazu gib es das nachfolgende sehr kurze Beispiel, welches eine XML in folgendem Format erstellt.
<Product-List>
<Product>
<Name>Road-750 Black, 52</Name>
<ProductNumber>BK-R19B-52</ProductNumber>
<ListPrice>539.9900</ListPrice>
</Product>
<Product>
<Name>Road-750 Black, 48</Name>
<ProductNumber>BK-R19B-48</ProductNumber>
<ListPrice>539.9900</ListPrice>
</Product>
</Product-List>
Dazu muss aus einer Abfrage übermitteltes XML direkt in eine Variable gespeichert werden. Die Prozedur sp_xml_preparedocument bietet die Möglichkeit XML für die weitere Verarbeitung einlesen und – wie der Name schon vermuten lässt – vorbereiten. Die Prozedur liefert eine Ganzzahl (!) lässt sich dann wiederum in der OPENXML-Anweisung nutzen. Es wird hier noch nichts zerlegt, sondern nur alle Spalten der entstehenden Ergebnismenge zurückgegeben, die zeigen soll, welcher Prozess im Hintergrund läuft.
DECLARE @productXML xml, @idoc int
SET @productXML = ( SELECT Name, ProductNumber, ListPrice
FROM Production.Product
FOR XML PATH('Product'),
ROOT('Product-List'))
EXEC sp_xml_preparedocument @idoc OUTPUT, @productXML
SELECT *
FROM OPENXML(@idoc, '/Product-List',1)
ORDER BY id
Die entstehende Tabelle wird mit einer WITH-Klausel genutzt, um benutzbare relationale Ergebnisse aus den XML-Daten zu erzeugen. Diese Rahmentabelle besitzt dabei die folgenden Spalten:
- id enthält einen eindeutigen Schlüssel des Wurzelelements. Der Wurzelknoten hat den Wert 0. Der datentyp ist bigint
- parentid enthält als bigint-Zahl den Schlüssel des Eltern- bzw. des übergeordneten Knotens eines Knotens (z.B. Element, Attribut, Textknoten, Namensraum, Prozessoranweisung).
- nodetype enthält den Knotentyp, wie er auch im DOM (Document Object Modell) genutzt wird. Der Datentyp ist int. Die Definitionen lauten wie folgt:
- 1 = Elementknoten
- 2 = Attributknoten
- 3 = Textknoten
- localname, der Datentyp ist nvarchar, enthält den lokalen Namen von Elementen oder Attributen und NULL, wenn kein Element oder Attribut abgerufen wurde.
- prefix enthält als nvarchar den Namensraumpräfix des Elements
- namespaceuri (nvarchar) enthält den Namensraum-URI des Elementes. NULL, wenn kein Namensraum angegeben wurde.
- datatype enthält als nvarchar Datentyp den XML-Datentyp des Elements oder Attributs.
- prev enthält als bigint den Schlüssel des vorhergehenden Elements auf der Geschwister-Achse. NULL, wenn es das erste Element ist.
- text enthält als ntext den Inhalt des Attributs/Elements. NULL, wenn kein Inhalt/Wert vorhanden ist.
Nun, da das XML-Dokument vorbereitet wurde, soll es nun zerlegt werden. Das Zerlegen ist Aufgabe der Funktion OPENXML, wobei für die Zuordnung von XML-Knoten zu relationalen Spalten XPath zum Einsatz kommt. Im Normalfall genügen hier einfache XPath-Ausdrücke. Allerdings gibt es auch Filter, die in XPath angegeben werden können, und sehr komplex werden können. Die allgemeine Syntax von OPENXML lautet:
OPENXML( idoc int [ in] ,
rowpattern nvarchar [ in ] ,
[ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Die Parameter und dessen Bedeutung, welche an die Funktion übegeben werden können, zeigt die folgene Liste kurz auf.
- idoc erwartet das Dokumenthandle, welches durch die Prozedur sp_xml_preparedocument erstellt wird, und welches die interne Repräsentation eines XML-Dokuments enthält.
- rowpattern eine XPath-Abfrage,welche die Teilbäume im XML-Dokument adressiert und ausgewählt. Die ausgewählten Elemente stehen als Zeilen für die weitere Verarbeitung zur Verfügung.
- flags erwartet eine Ganzzahl, mit dessen Hilfe festgelegt wird, wie die XML-Daten zu relationalen Zeilen zugeordnet werden. Zusätzlich regelt der Parameter das Handling für die Überlaufspalte(n). Folgene Liste gibt eine kurze Übersicht
- 0 legt fest, dass die Spalten aus den Attributen erstellt werden sollen (attributzent-rierte Zuordnung).
- 1 legt ebenfalls die attributzentrierte Zuordnung fest. Wenn zusätzlich XML_ELEMENTS angegeben wird, dann werden nach der attributzentrierten Verarbeitung die restlichen Spalten elementzentriert zugeordnet.
- 2 legt fest, dass die elementzentrierte Zuordnung verwendet wird. Wenn XML_ATTRIBUTES zusätzlich angegeben wird, dann wird die attributzentrierte Zuordnung auf die restlichen Spalten angewandt.
- 8 legt in Kombination mit XML_ELEMENTS oder XML_ATTRIBUTES fest, dass die Daten nicht in die Überlaufeigenschaft @mp:xmltext kopiert werden.
Die Zerlegung soll an einen einfachen XML Dokument demonstriert werden.
SELECT *
FROM OPENXML(@idoc, '/Product-List/Product',2)
WITH (Name varchar(50),
ProductNumber varchar(25),
ListPrice money)
Innerhalb der Variable @idoc besitzt man ein Dokumenthandle auf XML-Daten. Der XPath-Ausdruck des Beispieles wählt die einzelnen Product-Elemente über Wurzelknoten, Wurzelelement Product-List und schließlich Nennung von Product aus. Der Wert 2 gibt an, dass man die elementzentrierte Zuordnung benutzt. Innerhalb der WITH-Klausel ruft man nun einzelne Spalten auf und weist ihnen Datentypen zu. Ist ein Element Name und eine Spalte Name vorhanden, dann sorgt die elementzentrierte Zuordnung dafür, dass die Werte dieses Elements genau in diese Spalte kopiert werden.
Eine Umbenennung innerhalb der WITH-Klausel mit dieser sehr einfachen Methode der Umwandlung ist nicht möglich ist. Es ist dagegen möglich, dies in der SELECT-Anweisung direkt vorzunehmen. Das nächste kurze Beispiel zeigt, wie dies sogar unter Verwendung einer Funktion gelingen kann. Dieser Anweisung greift auf Daten wie auf die einer CTE oder einer abgeleiteten Tabelle zu. Ist dies nicht ausreichend, kann man auch noch mit XPath arbeiten und überaus individuelle Zuordnungen ausführen.
SELECT SUBSTRING(ProductNumber, 1, 10) AS [P-No],
ListPrice AS Price
FROM OPENXML(@idoc, '/Product-List/Product',2)
WITH (ProductNumber varchar(25),
Name varchar(50),
ListPrice money)
Das Ergebniss dieser Abfrage ist folgene kleine Tabelle:
| P-No | Price |
|---|---|
| BK-R19B-52 | 539,99 |
| BK-R19B-48 | 539,99 |