SELECT auf einen Datensatz mit JOIN auf ein XML Attribut

[Wurzel]

Guten Tag,

mir sind im Moment nicht die Mischbarkeit bzw. die Grenzen von SQL und XML bekannt. Wir setzen Komponenten ein (DevExpress), die eine Bindung zu Datenbanktabellen erfordern. Diese Tabellen sind von der Struktur her vorgegeben. Ein kleines Beispiel:

Termintabelle:
Start - datetime (Start des Termins)
End - datetime (Ende des Termins)
Subject - nvarchar (Betreff des Termins)
ResourceID - ntext (Mitarbeiter die an dem Ereignis teilnehmen)

Das Feld ResourceID enthält nicht, wie anzunehmen den Verweis auf einen dazugehörigen Datensatz, sondern den Verweis auf mehrere Datensätze in Form eines XML Strings. Der XML String ist Typisiert und enthält die Auflistung der MitarbeiterIDs aus der Mitarbeiter Tabelle.

Nun die Frage:
Ist es im SQL Server 2005 möglich, mit nur einem SELECT Statement einen Datensatz aus der Termintabelle zu ziehen, der über einen JOIN auf eine XML Tabelle verbunden ist, die sich aber in einem Attribut (ResourceID) aus dem zuziehenden Datensatz befindet? Also das der Datensatz auf sich selbst referenziert, jedoch nicht auf den ganzen Datensatz, sondern auf eine Tabelle aus einem Feld im Datensatz. Ohne eine SP oder UDF zu benutzen?

Ich wäre dankbar für ein einfaches Beispiel oder Links zu diesem oder einem ähnlich Thema.

Gruß Raimund

[O. Helper]

Hallo Raimund,

zufällig beschäfftige ich mich auch gerade etwas mit der Verwendung von XML im MS SQL Server.

Ja, prinzipiell geht es, Beispiel siehe unten (entspricht nicht ganz Deinem Szenario, veranschaulicht es aber).
Der Datentyp "ntext" ist bei Dir natürlich ein Handycap, nvarchar(max) wäre besser, XML ideal, da man darüber auch indizieren kann (so weit bin ich noch nicht ).

[highlight=SQL]SET NOCOUNT ON
-- Mitarbeiter einstellen
CREATE TABLE #Mit(MitID varchar(20), Mitarbeiter varchar(30));
INSERT INTO #Mit VALUES ('1', 'Helper');
INSERT INTO #Mit VALUES ('2', 'Mustermann');
INSERT INTO #Mit VALUES ('3', 'Musterfrau');
GO
-- Termine anlegene
CREATE TABLE #Termin (Nr int, Von varchar(10), Mit xml);
INSERT INTO #Termin
VALUES (1, '20090311', N'<ROOT>
<Mitarbeiter MitID="1"/>
<Mitarbeiter MitID="3"/>
</ROOT>');
INSERT INTO #Termin
VALUES (2, '20090312', N'<ROOT>
<Mitarbeiter MitID="1"/>
<Mitarbeiter MitID="2"/>
</ROOT>');

SELECT Termin.*, MIT.Mitarbeiter
FROM (SELECT #Termin.Nr, #Termin.Von,
CONVERT(varchar(25), T2.Loc.query('.')) As MitarbeiterXML,
T2.Loc.query('.').value('(/Mitarbeiter/@MitID)[1]', 'int') AS MitID
FROM #Termin
CROSS APPLY Mit.nodes('/ROOT/Mitarbeiter') as T2(Loc)
) AS Termin
INNER JOIN #Mit AS MIT
ON Termin.MitID = MIT.MitID
GO

DROP TABLE #Termin;
DROP TABLE #Mit;[/highlight]
Ergebnis:
[highlight=CODE]Nr Von MitarbeiterXML MitID Mitarbeiter
----------- ---------- ------------------------- ----------- ------------------------------
1 20090311 <Mitarbeiter MitID="1"/> 1 Helper
1 20090311 <Mitarbeiter MitID="3"/> 3 Musterfrau
2 20090312 <Mitarbeiter MitID="1"/> 1 Helper
2 20090312 <Mitarbeiter MitID="2"/> 2 Mustermann[/highlight]

[Wurzel]

Vielen Dank

Danke, genau das habe ich gesucht

SELECT Termin.*, Mitarbeiter.*
FROM (SELECT Termin.lTerminID, Termin.dtStart,
		TerminXML.Loc.query('.').value('(/ResourceId/@Value)[1]', 'int') AS MitID
	FROM Termin
		CROSS APPLY 
			strResourceId.nodes('/ResourceIds/ResourceId') AS TerminXML(Loc)
	) AS Termin
INNER JOIN Mitarbeiter AS Mitarbeiter ON Termin.MitID = Mitarbeiter.lMitarbeiterID

Diese Lösung hat jetzt mit der XtraScheduler Komponenten funktioniert.

Ich werde jetzt noch einmal schauen was ich mit "loc" (local?) und "query" so alles anstellen kann.

Edit: Ich konnte den Datentyp von Termin.ResourceId ohne Probleme von ntext auf xml umändern.

[O. Helper]

Freut mich, helfen zu konnten.

"loc" (local?)

Das ist bloß ein Name und hat nichts zu bedeuten.
Mit AS TerminXML(Loc) definierst Du eine Tabellenalias "TerminXML" und ein Feldalias "Loc", auf das Du dann im restlichem Statement referenzieren kannst. Das kann man also benamseln, wie man möchte.