Abflachung von hierarchischen XML in SQL mit Hilfe des Knoten () - Methode

Ich habe eine Gespeicherte Prozedur, die dauert ein XML-Dokument als parameter ähnlich wie in einer Struktur, die der folgenden:

<grandparent name="grandpa bob">
  <parent name="papa john">
    <children>
      <child name="mark" />
      <child name="cindy" />
    </children>
  </parent>
  <parent name="papa henry">
    <children>
      <child name="mary" />
    </children>
  </parent>
</grandparent>

Meine Forderung ist zu "glätten" diese Daten, so dass es eingefügt werden kann in eine temporäre Tabelle und manipuliert weiter unten Verfahren, so dass die oben genannten XML wird:

Grandparent Name Parent Name     Child Name
---------------- --------------- ---------------
grandpa bob      papa john       mark
grandpa bob      papa john       cindy
grandpa bob      papa henry      mary

Dies ist derzeit erfolgt mithilfe der SQL Server-XML-Knoten:

SELECT
    VIRT.node.value('../../../@name','varchar(15)') 'Grandparent Name',
    VIRT.node.value('../../@name','varchar(15)') 'Parent Name',
    VIRT.node.value('@name','varchar(15)') 'Child Name'
FROM
    @xmlFamilyTree.nodes('/grandparent/parent/children/child') AS VIRT(node)

Dies funktioniert gut, bis ich starten werfen große Mengen von Daten in das Verfahren (d.h. 1000+ child Knoten), an welcher Stelle diese zum Stillstand und dauert zwischen 1 und 2 Minuten ausführen. Ich denke, dass dies möglicherweise aufgrund der Tatsache, dass ich ab und an die Unterste Ebene (<child) und dann durchqueren sichern Sie das XML-Dokument für jedes vorkommen. Würde die Aufteilung dieser einzelnen Abfrage in 3 Teile (eine pro Knoten, ich brauche Daten aus) die Leistung verbessern hier? Da keiner von diesen Knoten "keys" auf Sie, die ich benutzen konnte, um die Verknüpfung wieder mit, könnte mir jemand irgendwelche Hinweise, wie ich vielleicht in der Lage zu gehen über das tun dies?

  • Der Prozess der Erstellung Zeile-basierte Daten aus xml in der Regel als "Schreddern", btw.
InformationsquelleAutor Matt Weldon | 2011-04-27
  1. 7

    Ich habe wohl meine Frage selbst beantwortet nach ein bisschen mehr auf der Suche rund um online:

    SELECT
    grandparent.gname.value('@name', 'VARCHAR(15)'),
    parent.pname.value('@name', 'VARCHAR(15)'),
    child.cname.value('@name', 'VARCHAR(15)')
FROM
    @xmlFamilyTree.nodes('/grandparent') AS grandparent(gname)
CROSS APPLY
    grandparent.gname.nodes('*') AS parent(pname)
CROSS APPLY
    parent.pname.nodes('children/*') AS child(cname)

    Mit CROSS APPLY ich kann wählen Sie die top-level - grandparent Knoten und verwenden Sie diese schaltäche, um das Kind parent Knoten und so weiter. Mit dieser Methode habe ich meine Abfrage ausführen, in etwa 1 minute 30 Sekunden unten um 6 Sekunden.

    Interessanterweise, wenn ich den "alten" OPEN XML - Methode zum abrufen der gleichen Daten, führt die Abfrage in 1 Sekunde!

    Es scheint, wie Sie können Ansatz die Verwendung dieser beiden Techniken, die auf einer Fall-zu-Fall-basis in Abhängigkeit von der erwarteten Größe/Komplexität des Dokuments weitergegeben werden.

    • Funktioniert das in der SQLServer2005? Ich bekomme eine Fehlermeldung (mit einem sehr ähnlichen Abfrage) auf der ersten KREUZ WENDEN - also Wo Sie sagen grandparent.gname.nodes bekomme ich Invalid object name datatask.dt.nodes (das ist mein äquivalent). Das ist eigentlich das, was ich erwarten würde, als eine standard-Abfrage wird nicht lassen Sie ein alias verwenden, die innerhalb der gleichen FROM Aussage...
    • Die großen Werke, die in sql server 2012, vielen Dank für diese Antwort.
    InformationsquelleAutor Matt Weldon
Schreibe einen Kommentar