There is a table in the database where there are a lot of records, but I am only interested in ~ 100,000.

Namely, I'm interested in the XML field.

I need to get the name of the node and its value and put in a temporary table.

I make an obvious query:

SELECT aevalue('(local-name(.))[1]','nvarchar(255)') AS NodeName, aevalue('(text())[1]','nvarchar(255)') AS Value INTO #TMP FROM [TABLE] CROSS APPLY XDoc.nodes('root/SomeNode/*') a(e) WHERE EXISTS([Какое-то условие отбора])

So, it runs for a very long time ... Is it possible to somehow somehow get the desired result, or it is impossible to squeeze more speed out of XML?

XML example:

 <root> <SomeNode> <f1><f1/> <f3><f3/> <f4><f4/> <f5><f5/> </SomeNode> <SomeNode> <f3><f3/> <f4><f4/> <f5><f5/> </SomeNode> <SomeNode> <f3><f3/> <f5><f5/> </SomeNode> </root>
  • one
    Can you see an example of the value of an XML field? - Nick Proskuryakov
  • @NickProskuryakov, Attached an example. That is, N SomeNode is nested in Root, and inside SomeNode there can be N nodes from which just need to get data. - iluxa1810
  • @NickProskuryakov, tried also in CROSS APPLY to do filtering excluding empty values, but it didn’t help much. - iluxa1810
  • @ i-one, no. Just filtering on some range of PK. - iluxa1810
  • And if you write a CLR function or do it through CTE, will there be some profit? - iluxa1810

0