XPath in T-SQL-Abfrage

Ich habe zwei Tabellen, XMLtable und filterTable .

Ich benötige alle XMLtable.ID Werte aus XMLtable wobei die data in Col_X MyElement , deren Inhalt filterColumn in filterTable .

Das XML für jede Zeile in Col_X kann mehrere MyElement 's enthalten, und ich möchte diese ID falls irgendwelche dieser Elemente mit einem der Werte in filterColumn .

Das Problem ist, dass diese Spalten tatsächlich von varchar(max) datatyp sind, und die Tabelle selbst ist riesig (wie 50GB riesig). Also muss diese Abfrage so optimiert wie möglich sein.

Hier ist ein Beispiel dafür, wo ich jetzt bin, die nur die Zeile zurückgibt, in der das erste passende Element gleich einer von denen ist, die ich suche. pathen einer Fülle von verschiedenen Fehlermeldungen kann ich nicht scheinen, dies zu ändern, um mit allen der gleichen benannten Elemente zu vergleichen, wie ich will.

 SELECT ID, CAST(Col_X AS XML).value('(//*[local-name()=''MyElement''])', N'varchar(25)') FROM XMLtable 

… und dann die Ergebnisse mit filterTable . Das dauert schon 5+ Minuten.

Was ich zu erreichen versuche, ist so etwas wie:

 SELECT ID FROM XMLtable WHERE CAST(Col_X AS XML).query('(//*[local-name()=''MyElement''])') IN (SELECT filterColumn FROM filterTable) 

Der einzige path, den ich derzeit erreichen kann, ist, den LIKE-Operator zu benutzen, der tausendmal länger dauert

Nun ist es offensichtlich keine Option, die datatypen der Spalten oder irgendetwas anderes zu ändern. Hier muss ich arbeiten. 🙂

Versuche dies:

 SELECT ID, MyElementValue FROM ( SELECT ID, myE.value('(./text())[1]', N'VARCHAR(25)') AS 'MyElementValue' FROM XMLTable CROSS APPLY (SELECT CAST(Col_X AS XML)) as X(Col_X) CROSS APPLY X.Col_X.nodes('(//*[local-name()="MyElement"])') as T2(myE) ) T1 WHERE MyElementValue IN (SELECT filterColumn FROM filterTable) 

und das:

 SELECT ID, MyElementValue FROM ( SELECT ID, myE.value('(./text())[1]', N'VARCHAR(25)') AS 'MyElementValue' FROM XMLTable CROSS APPLY (SELECT CAST(Col_X AS XML)) as X(Col_X) CROSS APPLY X.Col_X.nodes('//MyElement') as T2(myE) ) T1 WHERE MyElementValue IN (SELECT filterColumn FROM filterTable) 

Aktualisieren

Ich denke, dass Sie erleben, was hier beschrieben wird. Berechnen Sie Skalare, Ausdrücke und Ausführungsplan performance . Die Besetzung in XML wird auf jeden Aufruf der Wertfunktion verschoben. Der Test, den Sie machen sollten, ist, den datatyp von Col_X in XML zu ändern.

Wenn das nicht eine Option ist, können Sie die Zeilen, die Sie von XMLTable benötigen, in eine temporäre Tabelle abfragen, die eine XML-Spalte hat und dann die Abfrage oben gegen die temporäre Tabelle ohne die Notwendigkeit, in XML zu casting.

 CREATE TABLE #XMLTable ( ID int, Col_X xml ) INSERT INTO #XMLTable(ID, Col_X) SELECT ID, Col_X FROM XMLTable SELECT ID, MyElementValue FROM ( SELECT ID, myE.value('(./text())[1]', N'varchar(25)') AS 'MyElementValue' FROM #XMLTable CROSS APPLY Col_X.nodes('//MyElement') as T2(myE) ) T1 WHERE MyElementValue IN (SELECT filterColumn FROM filterTable) DROP TABLE #XMLTable 

Du könntest so etwas ausprobieren. Es funktioniert zumindest funktionell, was du willst, glaube ich. Sie müssen ihre performance mit Ihrem datasatz empirisch erforschen.

 SELECT ID FROM ( SELECT xt.ID, CAST(xt.Col_X AS XML) [content] FROM XMLTable AS xt ) AS src INNER JOIN FilterTable AS f ON f.filterColumn IN ( SELECT elt.value('.', 'varchar(25)') FROM src.content.nodes('//MyElement') AS T(elt) ) 

Ich habe endlich diese Arbeit und mit viel besserer performance als ich erwartet hatte. Unten ist das Skript, das schließlich das richtige Ergebnis in 5 – 6 Minuten produzierte.

 SELECT ID, myE.value('.', N'VARCHAR(25)') AS 'MyElementValue' FROM (SELECT ID, CAST(Col_X AS XML) AS Col_X FROM XMLTable) T1 CROSS APPLY Col_X.nodes('(//*[local-name()=''MyElement''])' T2(myE) WHERE myE.value('.', N'varchar(25)') IN (SELECT filterColumn FROM filterTable) 

Danke für die Hilfe der Leute!