dtcSoftware - powerShellDba

📌 XML als erster Bürger in SQL Server

GRUNDLAGEN

Seit SQL Server 2005 ist XML ein vollwertiger Datentyp. Sie können XML-Spalten in Tabellen anlegen, XML-Dokumente als Parameter an Prozeduren übergeben und mit XQuery (einer Teilmenge von XPath/XQuery) abfragen. Die wichtigsten Methoden sind:

.nodes() – bricht XML in relationale Zeilen auf (Shredding)
.value() – extrahiert einen einzelnen Wert aus XML
.exist() – prüft, ob ein bestimmter Knoten existiert
.query() – gibt ein XML-Fragment zurück
.modify() – ändert den Inhalt einer XML-Spalte

In der Praxis ist XML ideal für semi-strukturierte Daten, flexible Schnittstellen oder EAV‑Modelle.

💡 Wichtig: XML-Operationen sind nicht immer schnell. Bei großen Datenmengen können XML-Indizes (primär und sekundär) helfen, die Performance zu verbessern.

🔧 XML aufbrechen (Shredding) – mit nodes() und value()

XQUERY

Das wichtigste Muster: Eine XML-Variable oder Spalte wird mit CROSS APPLY .nodes(...) in Zeilen zerteilt. Jede Zeile repräsentiert einen Knoten, aus dem mit .value() einzelne Werte extrahiert werden.

DECLARE @xml XML = N'
<Orders>
  <Order OrderId="1" Customer="Meier">
    <Item>Laptop</Item>
    <Price>999</Price>
  </Order>
  <Order OrderId="2" Customer="Schulz">
    <Item>Maus</Item>
    <Price>29</Price>
  </Order>
</Orders>';

-- Zeilenweise Ausgabe der Bestellungen
SELECT
  OrderId = XOrder.value('@OrderId', 'INT'),
  Customer = XOrder.value('@Customer', 'VARCHAR(50)'),
  Item = XOrder.value('(Item)[1]', 'VARCHAR(100)'),
  Price = XOrder.value('(Price)[1]', 'DECIMAL(10,2)')
FROM @xml.nodes('/Orders/Order') AS XTbl(XOrder);

💡 Hinweis: Der Ausdruck (Item)[1] ist nötig, weil .value() einen einzelnen Wert erwartet – falls mehrere <Item>-Knoten unter einem Order existieren, würden Sie sonst einen Fehler erhalten.

📤 XML erzeugen – FOR XML PATH, RAW, AUTO

SERIALISIERUNG

Mit FOR XML können Sie Ergebnismengen direkt in XML umwandeln. Besonders flexibel ist die FOR XML PATH-Syntax, die es erlaubt, benutzerdefinierte XML-Strukturen zu bauen.

-- Einfaches Beispiel: Kunden als XML-Elemente
SELECT
  CustomerName AS [@Name], -- Attribut
  City AS [City], -- Element
  Country AS [Country]
FROM dbo.Customers
FOR XML PATH('Customer'), ROOT('Customers');

-- Verschachtelte Struktur (Parent/Child)
SELECT
  o.OrderId AS [@OrderId],
  (
    SELECT ProductName AS [Product]
    FROM dbo.OrderDetails od
    WHERE od.OrderId = o.OrderId
    FOR XML PATH('Item'), TYPE
  )
FROM dbo.Orders o
FOR XML PATH('Order'), ROOT('Orders');

⚠️ Wichtig: Das TYPE-Schlüsselwort bei Unterabfragen sorgt dafür, dass das innere XML als XML-Typ und nicht als String behandelt wird – sonst könnten Zeichen maskiert werden.

🔍 Existenzprüfung und Teil-XML – exist() & query()

XQUERY

Mit .exist() können Sie Zeilen filtern, die bestimmte XML-Bedingungen erfüllen. .query() extrahiert ein XML-Fragment aus der Spalte.

-- Alle Datensätze, bei denen im XML ein Property mit Wert "High" vorkommt
SELECT ManagedEntityRowId
FROM dbo.vManagedEntityProperty
WHERE PropertyXML.exist('/Root/Property[. = "High"]') = 1;

-- Teil-XML extrahieren (z. B. alle Property-Elemente)
SELECT
  ManagedEntityRowId,
  PropertyXML.query('/Root/Property') AS OnlyProperties
FROM dbo.vManagedEntityProperty;

💡 Tipp: .exist() kann auch in WHERE-Klauseln verwendet werden – der Optimizer kann sogar XML-Indizes nutzen, wenn vorhanden.

📊 Performance – XML-Indizes (primär & sekundär)

INDEXING

Wenn Sie häufig Abfragen auf XML-Spalten durchführen (besonders mit .exist() oder .value()), lohnt sich ein XML-Index. Der primäre XML-Index erzeugt eine persistente B‑Baum-Struktur über alle Knoten. Sekundäre Indizes (PATH, VALUE, PROPERTY) beschleunigen bestimmte XQuery-Typen.

-- Primären XML-Index erstellen (benötigt einen Clustered Index auf der Tabelle)
CREATE PRIMARY XML INDEX PXML_PropertyXML ON dbo.vManagedEntityProperty (PropertyXML);

-- Sekundäre Indizes (optional)
CREATE XML INDEX SXML_Path_PropertyXML ON dbo.vManagedEntityProperty (PropertyXML)
  USING XML INDEX PXML_PropertyXML FOR PATH;

CREATE XML INDEX SXML_Value_PropertyXML ON dbo.vManagedEntityProperty (PropertyXML)
  USING XML INDEX PXML_PropertyXML FOR VALUE;

CREATE XML INDEX SXML_Property_PropertyXML ON dbo.vManagedEntityProperty (PropertyXML)
  USING XML INDEX PXML_PropertyXML FOR PROPERTY;

📌 Hinweis: XML-Indizes verbrauchen zusätzlichen Speicher und verlangsamen INSERT/UPDATE–Operationen. Sie sollten nur bei leseintensiven Workloads eingesetzt werden.

📦 XML als Parameter – Flexible Stored Procedures

PROCEDUREN

XML eignet sich hervorragend, um eine variable Anzahl von Werten an eine Prozedur zu übergeben, ohne mehrere Parameter oder eine temporäre Tabelle zu benötigen.

CREATE PROCEDURE dbo.InsertMultipleOrders
  @OrdersXML XML
AS
BEGIN
  INSERT INTO dbo.Orders (CustomerId, OrderDate, TotalAmount)
  SELECT
    XOrder.value('@CustomerId', 'INT'),
    XOrder.value('@OrderDate', 'DATETIME'),
    XOrder.value('@TotalAmount', 'DECIMAL(10,2)')
  FROM @OrdersXML.nodes('/Orders/Order') AS XTbl(XOrder);
END;

-- Aufruf
EXEC dbo.InsertMultipleOrders N'
<Orders>
  <Order CustomerId="1" OrderDate="2025-03-15" TotalAmount="150.00"/>
  <Order CustomerId="2" OrderDate="2025-03-16" TotalAmount="89.90"/>
</Orders>';

💡 Vorteil: Keine Begrenzung auf eine bestimmte Anzahl von Zeilen, keine String-Konkatenation für dynamische SQL. Nachteil: Die XML-Parsing-Kosten fallen einmalig an.

⚙️ Praxisbeispiel: Dynamisches UPDATE mit XML-Eigenschaften

EAV-MODEL

Ein häufiges Muster (EAV – Entity‑Attribute‑Value) speichert Eigenschaften in XML. Eine Prozedur wie ReadProperties extrahiert diese und aktualisiert eine flache Zieltabelle. Hier ein optimiertes Grundgerüst ohne Cursor.

CREATE OR ALTER PROCEDURE dbo.UpdateFromXmlProperties
  @SourceTable SYSNAME,
  @TargetTable SYSNAME,
  @IdColumn SYSNAME,
  @XmlColumn SYSNAME
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX) = N'';

  -- Dynamische Generierung eines Updates, das alle Properties aus der Quelltabelle in die Zieltabelle überträgt
  WITH PropertyList AS (
    SELECT DISTINCT PropertyName = x.value('local-name(.)', 'SYSNAME')
    FROM @SourceTable s
    CROSS APPLY s.CAST(XmlData AS XML).nodes('/Root/*') AS X(Prop)
    -- Hier müsste die Tabelle @SourceTable als dynamische Tabelle referenziert werden – das ist nur eine Prinzipskizze
  )
  SELECT @sql = STRING_AGG(N' t.' + QUOTENAME(PropertyName) + N' = s.' + QUOTENAME(PropertyName), N',' + CHAR(13))
  FROM PropertyList;

  SET @sql = N'UPDATE t SET ' + @sql + N' FROM dbo.' + QUOTENAME(@TargetTable) + N' t
JOIN dbo.' + QUOTENAME(@SourceTable) + N' s ON t.' + QUOTENAME(@IdColumn) + N' = s.' + QUOTENAME(@IdColumn);

  EXEC sys.sp_executesql @sql;
END;

⚠️ Hinweis: Dieses Beispiel ist stark vereinfacht. In der Praxis müssen Sie dynamisches SQL sehr sorgfältig bauen, SQL‑Injection vermeiden (QUOTENAME) und die Performance im Auge behalten. Oft ist eine Cursor‑basierte Lösung wie die ursprüngliche ReadProperties für kleine Property-Mengen akzeptabel.

🌐 XML-Namespaces – korrekt abfragen

XQUERY

Enthält Ihr XML Namespaces (z. B. aus Webservices), müssen Sie diese mit WITH XMLNAMESPACES deklarieren, bevor Sie XQuery-Ausdrücke verwenden.

DECLARE @xml XML = N'
<root xmlns:ns="http://example.com/ns">
  <ns:Customer id="1">Meier</ns:Customer>
</root>';

WITH XMLNAMESPACES (DEFAULT 'http://example.com/ns')
SELECT
  XCust.value('@id', 'INT') AS CustomerId,
  XCust.value('.', 'VARCHAR(50)') AS CustomerName
FROM @xml.nodes('/root/Customer') AS XTbl(XCust);

💡 Tipp: Ein häufiger Fehler sind leere Ergebnismengen wegen vergessener Namespaces. Nutzen Sie WITH XMLNAMESPACES oder fragen Sie mit /*:localname (Wildcard) ab (nicht empfohlen).

✅ Best Practices für XML in SQL Server

EMPFEHLUNGEN

Typisierte XML-Spalten verwenden: Hinterlegen Sie ein XML‑Schema, um die Gültigkeit zu prüfen und bessere Performance zu erzielen.
Shredding bevorzugt: Wenn Sie XML häufig nach einzelnen Werten durchsuchen, sollten Sie diese Werte in relationale Spalten extrahieren (ETL‑Prozess).
Size matters: XML-Spalten sollten nicht für sehr große Dokumente (MB+) genutzt werden – besser FILESTREAM oder separate Dateien.
FOR XML mit TYPE: Verwenden Sie TYPE bei Unterabfragen, um saubere XML‑Strukturen zu behalten.
Indizes testen: Messen Sie den Gewinn von XML-Indizes mit realen Abfragen – sie sind nicht immer hilfreich.
Vermeiden Sie SELECT * FROM ... FOR XML AUTO in Produktion: Die generierte Struktur ist oft unübersichtlich und nicht vorhersehbar.
Parameter in sp_executesql nutzen: Wenn Sie dynamische XML-Pfade oder Werte einsetzen, vermeiden Sie String‑Ketten – verwenden Sie besser vollparametrisierte Aufrufe.

📌 Fazit – XML als Flexibilitätsbooster

RESÜMEE

XML ist in SQL Server ein vollwertiges Werkzeug, das Sie für flexible Schnittstellen, variable Property-Modelle oder den Import hierarchischer Daten einsetzen können. Die Kombination aus Shredding (nodes/value), Erzeugung (FOR XML) und Indizierung erlaubt effiziente Lösungen. Achten Sie auf die Fallstricke (Namespaces, Performance bei großen Dokumenten) und nutzen Sie die erweiterte XQuery-Unterstützung.

Haben Sie konkrete XML‑Probleme in Ihrem SQL Server? Testen Sie die gezeigten Skripte in Ihrer Umgebung und passen Sie sie an Ihre Bedürfnisse an.

🔍 Weiterführende Microsoft‑Dokumentation: XML Data Type Methods | XML Indizes | FOR XML

📌 XML als erster Bürger in SQL Server

🔧 XML aufbrechen (Shredding) – mit nodes() und value()

📤 XML erzeugen – FOR XML PATH, RAW, AUTO

🔍 Existenzprüfung und Teil-XML – exist() & query()

📊 Performance – XML-Indizes (primär & sekundär)

📦 XML als Parameter – Flexible Stored Procedures

⚙️ Praxisbeispiel: Dynamisches UPDATE mit XML-Eigenschaften

🌐 XML-Namespaces – korrekt abfragen

✅ Best Practices für XML in SQL Server

📌 Fazit – XML als Flexibilitätsbooster

Impressum-Hinweis zur verantwortlichen Stelle