Brechen Sie XML-fileen mit SQL server in einzelne Zeilen

Hier ist was ich tue Ich lege eine XML-file in eine XML-Spalte ein, zusammen mit anderen Spalten wie datesstempeln etc. für die logging (die sind irrelevant für diese Anfrage)

Die file sieht so ähnlich aus:

<topLevelItem> <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> <topLevelItem> 

Mein Ziel ist es, das XML-Feld abzufragen und mit jedem <secondLevelItem> in der eigenen Zeile eine Ergebnismenge zu erhalten, in der XML-Form. Wie unten:

Reihe 1:

  <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> 

Zeile 2:

  <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> 

Zeile 3:

  <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> 

Dies muss mit Microsoft SQL server und ohne CLI erfolgen. Ich muss in der Lage sein zu deklarieren, welchen XML-Knoten ich den Breakdown anfangen möchte, da einige fileen zusätzliche XML-Elemente haben, die ich nicht benötige. So etwas wie, WHERE node = secondLevelItem .

Mit SQL server 2005 und neuer, gibt es hervorragende XQuery-Unterstützung in SQL server.

Dieser Ansatz würde hier die Methode .nodes() und .query() verwenden:

 SELECT Col.query('.') FROM dbo.YourTable CROSS APPLY YourXmlColumn.nodes('/topLevelItem/secondLevelItem') AS Tbl(Col) WHERE (some condition) 

Die .nodes() -function erzeugt eine "temporäre, inline" Tbl mit einer einzigen Spalte Col die einen XML-Eintrag für jeden Knoten in Ihrer XML-Spalte enthält, der mit dem XPath übereinstimmt (in diesem Fall: jedes <secondLevelItem> innerhalb des <topLevelItem> wird abgestimmt).

Da Sie das gesamte XML für jedes dieser XML-Elemente .query('.') , verwenden Sie einfach die .query('.') , .query('.') das komplette XML-Element zurückzugeben.

Verwenden Sie nodes () , um Ihr XML zu zerkleinern und verwenden Sie local-name () und sql: variable () , um den gewünschten Knoten zu finden, das XML mit der Abfrage () abzurufen.

 declare @NodeName varchar(100) = 'secondLevelItem' select TNquery('.') from YourTable cross apply XMLCol.nodes('//*[local-name()=sql:variable("@NodeName")]') as T(N) 

Wenn du das Original-XML nur einer gespeicherten Prozedur zuführst und es nicht aus einer Tabelle ziehst, kannst du folgendes machen.

 DECLARE @XML xml SET @XML = '<topLevelItem> <secondLevelItem> <secondLevelItemDetail>One</secondLevelItemDetail> <secondLevelItemAnotherDetail>One One</secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail>Two</secondLevelItemDetail> <secondLevelItemAnotherDetail>Two Two</secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail>Three</secondLevelItemDetail> <secondLevelItemAnotherDetail>Three Three</secondLevelItemAnotherDetail> </secondLevelItem> </topLevelItem>' SELECT Tbl.Col.query('.') FROM @XML.nodes('/topLevelItem/secondLevelItem') AS Tbl(Col)