Rekursive Abfrage in SP nach XML umformen und per VARCHAR(MAX) OUTPUT ausgeben...

jmar83

SQL-Guru
Beiträge
146
Hallo zusammen,
gegeben sei folgender Code:

Code:
USE test
  GO

IF OBJECTPROPERTY(object_id('dbo.sp_showTreeByRecursion'), N'IsProcedure') = 1
BEGIN
  DROP PROCEDURE dbo.sp_showTreeByRecursion
END
  GO

SET ANSI_NULLS ON
  GO

SET QUOTED_IDENTIFIER ON
  GO

CREATE PROCEDURE dbo.sp_showTreeByRecursion
  @parentNodeID    INT = NULL,
  @return        VARCHAR(MAX) = ''    OUTPUT
AS
BEGIN
----------------------------------------------------------------------
DECLARE
  @nodeID     INT,
  @nodeText   VARCHAR(32)

DECLARE Nodes_Cursor CURSOR LOCAL FOR

SELECT
  NodeID,
  NodeText

FROM Nodes

WHERE ISNULL(ParentNodeID, 0) = ISNULL(@parentNodeID, 0) AND ParentNodeID IS NOT NULL

ORDER BY NodeText ASC

OPEN Nodes_Cursor
FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText

WHILE @@FETCH_STATUS = 0

BEGIN
  PRINT @nodeText
  SET @return = @return + @nodeText
  IF (SELECT COUNT ('') FROM Nodes WHERE ParentNodeID = @nodeID) > 0
  BEGIN
    EXEC @parentNodeID = sp_showTreeByRecursion @nodeID, @return
  END
  FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText
END

CLOSE Nodes_Cursor
DEALLOCATE Nodes_Cursor

RETURN @parentNodeID
----------------------------------------------------------------------
END
  GO


Die Tabelle sieht so aus:
Unbenannt.jpg




Der Aufruf geht folgendermassen:

Code:
USE test
DECLARE @return2 VARCHAR(MAX)

SET @return2 = ''

EXEC sp_showTreeByRecursion null, @return2 OUTPUT

PRINT @return2


Das Problem ist: wenn ich mit "PRINT" DIREKT in der SP im Management Studio was ausgebe, wird es korrekt ausgegeben, alle Werte welche in der Tabelle enthalten sind:

Kleidung
Damenkleidung
Herrenkleidung
Lebensmittel
Gemüse
Broccoli
Kohlrabi
Getränke
Obst
Orangen
Pfirsich




Will ich aber die OUTPUT-Varaible "@return" verwenden, und diese bei der Rekursion immer wieder mitliefere geht die String-Konkatenation irgendwienicht. Der String, der zurückkommt ist nur "KleidungLebensmittel"

Kleidung
Damenkleidung
Herrenkleidung
Lebensmittel
Gemüse
Broccoli
Kohlrabi
Getränke
Obst
Orangen
Pfirsich
KleidungLebensmittel

(Der Rest oberhalb kommt vom "PRINT" welches DIREKT in der SP ist...)


Die Ausgabe sollte schlussendlich folgendermassen sein:

<TopNode>
<Lebensmittel>
</Lebensmittel>
<Kleidung>
</Kleidung>
</TopNode>


NodeID's, welche NICHT Teilmenge von ParentNodeID sind, sind demzufolge KEINE Knotenpunte/Katagorien, sondern eher Artikel halt... diese sollten folgendermasssen im XML sein:

<TopNode>
<Lebensmittel>
<Obst>Pfirsich</Obst>
<Obst>Orangen</Obst>
<Gemuese>Kohlrabi</Gemuese>
<Gemuese>Broccoli</Gemuese>
</Lebensmittel>
<Kleidung>
...analog oben...
</Kleidung>


Kann mir jemand helfen?
(Und nein, ich möchte KEIN CTE verwenden, des weiteren auch nicht auf der Ebene einer "höheren Programmiersprache das Problem lösen. T-SQL sollte das bewerstelligen können würde ich sagen!)

Aber irgendwie stehe ich ein wenig "auf dem Schlauch" damit...


Grüsse und vielen Dank.
Jan
 
Zuletzt bearbeitet von einem Moderator:
Werbung:
Also ich versteh irgendwie das ganze grade nicht rufst du aus der Prozedur die selbige wieder auf?

Jedenfalls existiert @return in deiner WHILE Schleife nur bei Kleidung und Lebensmittel. Ändere mal dein PRINT @nodeText (das wird immer zurück geliefert) in SELECT @nodeText,@return und du wirst sehen das @return nicht leer sondern gar nicht da ist.
 
Hallo Jan,

dein Endergebnis ist mit den Bordmitteln des SQL-Servers nicht ganz einfach zu erreichen, zumindest dann nicht, wenn es dynamisch sein soll und Attribute z.B. in verschiedenen Ebenen der Hierarchie auftauchen können. Um deine Hierarchie als geordneten Baum zurückzugeben habe ich einmal eine Abfrage aus meinem Fundus an deine Vorgaben angepasst:

Code:
WITH REK (ParentNodeID, NodeID, NodeText, Level, SRT)
AS (
SELECT ParentNodeID, NodeID, NodeText, 1 as Level, ROW_NUMBER() OVER (ORDER BY NodeID) as SRT
FROM dbo.Nodes
WHERE ParentNodeID IS NULL

UNION ALL

SELECT N.ParentNodeID, N.NodeID, N.NodeText, R.Level + 1 as Level, R.SRT * 100 + ROW_NUMBER() OVER (ORDER BY N.NodeID) as SRT
FROM dbo.Nodes N
INNER JOIN REK R
    ON N.ParentNodeID=R.NodeID
)


SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY LEFT(CAST(SRT as varchar(20))+'0000000', 7)) as IDN,
--SPACE((Level-1)*5)+
NodeText as NodeText
,LEFT(CAST(SRT as varchar(20))+'0000000', 7) as SRT,
Level as LV
--INTO #a
FROM REK
ORDER BY LEFT(CAST(SRT as varchar(20))+'0000000', 7)


Um aus diesem Ergebnis nun eine XML-Syntax mit allen korrekten Anfangs- und End-Tags zu erstellen musst du dich mit reiner T-SQL Programmierung ein wenig anstrengen.
Der SQL Server unterstützt zwar das Auslesen von Informationen aus XML-Konstrukten, hat aber keine Engine um solche Knoten-Werte zu erstellen, das musst du mit reiner Text-Zusammenführung erreichen.
(Zumindest nicht bis SQL Server 2012, vielleicht gibt's beim 2014er was).

Aber vielleicht hilft dir dieser Ansatz ja ein wenig, um deinen Code schlanker zu machen. Meinen Code kann man übrigens auch als View anlegen.

Viele Grüße,
Tommi
 
@Tommi Er hat aber ausdrücklich gesagt das er keine CTE möchte...

Ich hab keine Ahnung von T-SQL... Aber ganz allgemein gesprochen:

Ich denke es hängt an diesem Schnipsel... Einen Ausgabe-Parameter kann man in der Regel nicht als normale Übergabe verwenden(auch wenn Sie vom Typ OUTPUT ist)...
Code:
EXEC @parentNodeID = sp_showTreeByRecursion @nodeID, @return
 
Hallo zusammen
Vielen Dank für eure Feedbacks!
"dein Endergebnis ist mit den Bordmitteln des SQL-Servers nicht ganz einfach zu erreichen"

Theoretisch sollte das doch irgendwie möglich sein, T-SQL ist ja "Turing-vollständig", was meines Wissens nach heisst dass JEDER Ablauf/Algorithmus formuliert werden kann wie in einer höheren Programmiersprache auch.

Apropos höhere Programmiersprache: Etwas ähnliches was die Rekursion betrifft (welche ich im meinem Beispiel ja selbst implementiere!) habe ich mal mit Java gemacht, nämlich Verzeichnisbäume auslesen, und das Ergebnis in einer ArrayList<String> zurückgeben:

C:\test\
C:\test\1\
C:\test\1\1\
C:\test\1\2\
C:\test\3\1\
............

da Java aber "richtige" Rückgabevariablen hat (im Ggs. zu TSQL-SP's wo soviel ich weiss nur INT's zurückgegeben werden)
Was ich verwende ist ja nicht eine "richtige" Rückgabevariable, sondern eine Argumentvariable "by Reference" (=OUTPUT Deklaration) Daraus ergibt sich auch das Problem dass ich diese bei jeder Rekurion wieder mitliefern muss, und dabei geht irgendwas verloren...:-(


Java Code hier:

Code:
public static String[] getSubdirectories(final String aRemotePath)
    {
        File[] lTmpFileArr = null;

        String[] lRetStringArr = null;

        Vector<String> lVector = null;

        int lDirectoryCounter = 0;

        if ((aRemotePath != null) && (aRemotePath.intern() != ""))
        {
            final File lFile = new File(aRemotePath.trim());

            if (lFile.exists())
            {
                lTmpFileArr = lFile.listFiles();
                lVector = new Vector<String>();

                for (final File element : lTmpFileArr)
                {
                    if (element.isDirectory())
                    {
                        lDirectoryCounter++;
                        lVector.add(element.toString());
                    }
                }

                lRetStringArr = new String[lDirectoryCounter];

                for (int lCnt = 0; lCnt < lVector.size(); lCnt++)
                {
                    lRetStringArr[lCnt] = lVector.get(lCnt);
                }
            }
        }
        return lRetStringArr;
    }

    public static Vector<String> getSubDirectoryStructure(final String aSrcPath)
    {
        String[] lRetStringArr = null;

        if ((aSrcPath != null) && (aSrcPath.intern() != ""))
        {
            lRetStringArr = RecursiveDirectoryReader.getSubdirectories(aSrcPath);

            for (final String element : lRetStringArr)
            {
                RecursiveDirectoryReader.getSubDirectoryStructure(element);
                RecursiveDirectoryReader.mVector.add(element);
            }
        }

        Collections.sort(RecursiveDirectoryReader.mVector);

        return RecursiveDirectoryReader.mVector;
    }



Ich beziehe mich noch mal auf die Turing-Vollständigkeit von T-SQL, deshalb bin ich eigentlich davon überzeugt dass das irgendwie geht. Nur wie ist die Frage...? ;-)

Das Thema ist schinbar nicht ganz trivial...

Grüsse, Jan
 
Oder sollte ich besser eine UDF draus machen?

Da hätte man doch mehr Möglichkeiten auch ohne "OUTPUT" etwas zurückzuliefern.

Bei SP's kann man per "RETURN" ja nur INT zurückgeben, z.b. zum verifizieren ob die SP erfolgreich ausgeführt wurde oder nicht... alles andere muss man per OUTPUT-Argumentvariable "by Reference" realisieren. So sehe ich das zumindest...?

Grüsse,
Jan
 
Zuletzt bearbeitet:
@Distrilec: "Einen Ausgabe-Parameter kann man in der Regel nicht als normale Übergabe verwenden(auch wenn Sie vom Typ OUTPUT ist)..."

-> es kommt ja schon was zurück:

Kleidung
Damenkleidung
Herrenkleidung
Lebensmittel
Gemüse
Broccoli
Kohlrabi
Getränke
Obst
Orangen
Pfirsich
KleidungLebensmittel


Der Unterste Eintrag kommt von der ausgelesenen OUTPUT-Variable her!!
Der Rest vom "PRINT" direkt aus dem Methodenrumpf von "dbo.sp_showTreeByRecursion" (bringt nichts, also loggt nur in der Komsole im "Mangement Studio" (DB-Frontend vom M$)

Korrekt aufgelistet wär's ja mit dem Kategorien, es ist einfach nicht erkenntlich wie der Tree ist:

Kleidung
Damenkleidung
Herrenkleidung
Lebensmittel
Gemüse
Broccoli
Kohlrabi
Getränke
Obst
Orangen
Pfirsich


Beweis:

Kleidung
->Damenkleidung
->Herrenkleidung
Lebensmittel
->Gemüse
-->Broccoli
-->Kohlrabi
->Getränke
->Obst
-->Orangen
-->Pfirsich

Also an der Stelle vom "PRINT" kommt das richtige dabei Raus - damit müsste mit in Schritt 1 zuerst mal einen String konkatenieren. (die XML-Tags drum herum wäre dann das nächste... und als letztes die Sache mit denen Eintragen (Knoten/Artikel) , ich zitiere mich selbst: "NodeID's, welche NICHT Teilmenge von ParentNodeID sind, sind demzufolge KEINE Knotenpunte/Katagorien, sondern eher Artikel halt...")

Grüsse,
Jan
 
Zuletzt bearbeitet:
@jmar83 Das liegt daran das du dich im ersten Durchlauf befindest... In diesem Durchlauf ist es auch noch ein normaler Parameter! Bei allen rekursiv aufgerufenen Durchläufen schlägt eben diese Referenz fehl... Da man keinen Parameter mit übergeben kann...

(Hier einmal wieder der Hinweis: Ich hab überhaupt garkeine Ahnung von T-SQL... Das fiddel ich gerade einfach "as is" zusammen)
Das hier könnte aber dennoch dein Problem lösen. (Ist nur ein Versuch... Ich habe es weder getestet noch auf Syntax geprüft)
Code:
USE test
  GO

IF OBJECTPROPERTY(object_id('dbo.sp_showTreeByRecursion'), N'IsProcedure') = 1
BEGIN
  DROP PROCEDURE dbo.sp_showTreeByRecursion
END
  GO

SET ANSI_NULLS ON
  GO

SET QUOTED_IDENTIFIER ON
  GO

CREATE PROCEDURE dbo.sp_showTreeByRecursion
  @parentNodeID    INT = NULL,
  @return        VARCHAR(MAX) = ''    OUTPUT
AS
BEGIN
----------------------------------------------------------------------
DECLARE
  @nodeID     INT,
  @nodeText   VARCHAR(32)
  @tempVar    VARCHAR(MAX)

DECLARE Nodes_Cursor CURSOR LOCAL FOR

SELECT
  NodeID,
  NodeText

FROM Nodes

WHERE ISNULL(ParentNodeID, 0) = ISNULL(@parentNodeID, 0) AND ParentNodeID IS NOT NULL

ORDER BY NodeText ASC

OPEN Nodes_Cursor
FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText

WHILE @@FETCH_STATUS = 0

BEGIN
  PRINT @nodeText
  IF (SELECT COUNT ('') FROM Nodes WHERE ParentNodeID = @nodeID) > 0
  BEGIN
    EXEC @parentNodeID = sp_showTreeByRecursion @nodeID, @tempVar
  END
  SET @return = @return + @tempVar + @nodeText
  FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText
END

CLOSE Nodes_Cursor
DEALLOCATE Nodes_Cursor

RETURN @parentNodeID
----------------------------------------------------------------------
END
  GO
 
Hallo Jan,

Distrilec hat schon recht, in dem Exec-Aufruf fehlt hinter dem Parameter, der den Output-Wert empfangen soll, dass Schlüsselwort "OUTPUT".


Was meinen Code angeht, so befürworte ich diesen aus folgenden Gründen:
1. kompakter und schlanker Code
2. kann als View angelegt und so für mehrere Zwecke genutzt werden
3. es wird kein Cursor benötigt (was nur ein einer Prozedur überhaupt funktioniert)
4. es wird nur ein einzelnes Element angelegt
5. es ist mein Code ;)

Womit deine Programmierung funktionieren könne ist XML DML.
Hier ein Link. Aber das ist genau das was ich meinte, das ist nicht ganz so trivial wie es zunächst aussieht, insbesondere wenn du mit dynamischen Ausdrücken arbeiten möchtest.
https://msdn.microsoft.com/de-de/library/ms175466.aspx


Viele Grüße,
Tommi
 
@tommy: jetzt bin ich verwirrt! Ich hatte immer "OUTPUT" beim Aufruf (siehe 1. Post), "Distrilec" hat mich wohl falsch zitiert mit dem Code in Post #4? (?)

Mein Code vom 1. Post:

Code:
USE test
DECLARE @return2 VARCHAR(MAX)

SET @return2 = ''

EXEC sp_showTreeByRecursion null, @return2 OUTPUT

PRINT @return2


@Distrilect: Dein Code bringt das gleiche:

Code:
USE test
  GO

IF OBJECTPROPERTY(object_id('dbo.sp_showTreeByRecursion'), N'IsProcedure') = 1
BEGIN
  DROP PROCEDURE dbo.sp_showTreeByRecursion
END
  GO

SET ANSI_NULLS ON
  GO

SET QUOTED_IDENTIFIER ON
  GO

CREATE PROCEDURE dbo.sp_showTreeByRecursion
  @parentNodeID    INT = NULL,
  @return        VARCHAR(MAX) = ''    OUTPUT
AS
BEGIN
----------------------------------------------------------------------
DECLARE
  @nodeID     INT,
  @nodeText   VARCHAR(32),
  @tempVar    VARCHAR(MAX)

SET @tempVar = ''

DECLARE Nodes_Cursor CURSOR LOCAL FOR

SELECT
  NodeID,
  NodeText

FROM Nodes

WHERE ISNULL(ParentNodeID, 0) = ISNULL(@parentNodeID, 0) AND ParentNodeID IS NOT NULL

ORDER BY NodeText ASC

OPEN Nodes_Cursor
FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText

WHILE @@FETCH_STATUS = 0

BEGIN
  --PRINT @nodeText
  IF (SELECT COUNT ('') FROM Nodes WHERE ParentNodeID = @nodeID) > 0
  BEGIN
    EXEC @parentNodeID = sp_showTreeByRecursion @nodeID, @tempVar
  END
  SET @return = @return + @tempVar + @nodeText
  FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText
END



CLOSE Nodes_Cursor
DEALLOCATE Nodes_Cursor

RETURN @parentNodeID
----------------------------------------------------------------------
END
  GO



(String musste noch auf "Empty" gesetzt werden, sonst ist keine Konkatenation möglich)

Resultat von

EXEC sp_showTreeByRecursion null, @return2 OUTPUT
PRINT @return2

ist gleich:

"KleidungLebensmittel"


(Klar gibt es einfachere Ansätze sowas zu bewerkstelligen in T-SQL, ich komme aber eben aus dem nicht-SQL-Bereich und möchte ein wenig die Möglichkeiten ausprobieren.)

Grüsse, Jan
 
Im Exec-Aufruf innerhalb der Cursor-Schleife fehlt das Befehlswort "OUTPUT":

Code:
EXEC @parentNodeID = sp_showTreeByRecursion @nodeID, @tempVar
 
Diese Syntax... Verursacht die nur bei mir Schmerzen in der Hirnstamm-Gegend? Bin ich froh dass ich PL/SQL schreibe... Ein echter Krampf, dieses T-SQL... Und so schlecht zu lesen...
Aber das ist wahrscheinlich einfach nur gewöhnungssache?
 
Werbung:
Ich kenn sonst nur SQL-92, welches ich bisher (ganz begrenzt, meist nur create/read/update/delete/join) unter MySQL für irgendwelche selbstgebastelten Webseiten verwendet habe. Und dabei immer nur Einzeiler, eingebettet im Code. (Java, PHP, .NET)

Habe SQL immer gemieden, bin aber durch einen Job in Kontakt mit T-SQL/SQL Server gekommen, und irgendwie fasziniert davon. Kenne halt auch nicht viel anderes...


Ich habe jetzt noch ein OUTPUT gesetzt beim Aufruf innerhalb sich selbst:

Code:
USE test
  GO

IF OBJECTPROPERTY(object_id('dbo.sp_showTreeByRecursion'), N'IsProcedure') = 1
BEGIN
  DROP PROCEDURE dbo.sp_showTreeByRecursion
END
  GO

SET ANSI_NULLS ON
  GO

SET QUOTED_IDENTIFIER ON
  GO

CREATE PROCEDURE dbo.sp_showTreeByRecursion
  @parentNodeID    INT = NULL,
  @return        VARCHAR(MAX) = ''    OUTPUT
AS
BEGIN
----------------------------------------------------------------------
DECLARE
  @nodeID     INT,
  @nodeText   VARCHAR(32),
  @tempVar    VARCHAR(MAX)

SET @tempVar = ''

DECLARE Nodes_Cursor CURSOR LOCAL FOR

SELECT
  NodeID,
  NodeText

FROM Nodes

WHERE ISNULL(ParentNodeID, 0) = ISNULL(@parentNodeID, 0) AND ParentNodeID IS NOT NULL

ORDER BY NodeText ASC

OPEN Nodes_Cursor
FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText

WHILE @@FETCH_STATUS = 0

BEGIN
  PRINT @nodeText
  IF (SELECT COUNT ('') FROM Nodes WHERE ParentNodeID = @nodeID) > 0
  BEGIN
    EXEC @parentNodeID = sp_showTreeByRecursion @nodeID, @tempVar OUTPUT
  END
  SET @return = @return + @tempVar + @nodeText + ' '
  FETCH NEXT FROM Nodes_Cursor INTO @nodeID, @nodeText
END



CLOSE Nodes_Cursor
DEALLOCATE Nodes_Cursor

RETURN @parentNodeID
----------------------------------------------------------------------
END
  GO


das gibt dann folgendes aus:

Damenkleidung Herrenkleidung Kleidung Damenkleidung Herrenkleidung Broccoli Kohlrabi Gemüse Broccoli Kohlrabi Getränke Broccoli Kohlrabi Orangen Pfirsich Obst Lebensmittel


Irgendwie fehl noch ein IF beim Konkatenieren so wie es mir scheint. (etwa "Damenkleidung Herrenkleidung" am Anfang ist zuviel)

Langsam sehe ich den Wald vor lauter Bäumen nicht mehr...;-) Gar nicht mal so einfach, das alles Gedanklich nachzuvollziehen ehrlich gesagt...


GRüsse, Jan
 
Zurück
Oben