|
|
|
|
|
Comelio GmbH
|
Comelio-Blog > MS SQL Server > XML in T-SQL zerlegen XML in T-SQL zerlegen
XML zerlegenNeben 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:
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.
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:
|
Es kommt häufig vor, dass man nicht nur eine einfache Zuordnung benötigt, sondern die Daten weiter einschränken will. Entweder setzt man hier wie gewohnt eine WHERE-Klausel ein, sodass die Daten zunächst übertragen und nachher eingeschränkt werden, oder man filtert die zuzuordnenden Daten bereits vorher mit XPath heraus. Beide Varianten kommen im nächsten Beispiel zum Einsatz. Zunächst verlangt der XPath-Ausdruck alle Daten mit einen Listenpreis kleiner 200, bevor die WHERE-Klausel die Produktauswahl noch einmal auf diejenigen einschränkt, deren Name mit LL beginnt.
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.
SELECT Name, ListPrice FROM OPENXML(@idoc, '/Product-List/Product[ListPrice < 200]',2)
WITH (Name varchar(50),
ProductNumber varchar(25),
ListPrice money)
WHERE Name LIKE 'LL%'
Ein einsamer Datensatz bleibt in diesem Fall übrig.
| Name | ListPrice |
|---|---|
| LL Bottom Bracket | 53,99 |