XML MSSQL Performance Issue

Question

Sample data:

<Data>
  <row preis="2.4000" anzahl="72.0000" rabatt="0.0000" mwst="16.0000" bez="tägliche Standreinigung" adoku_nr="1694351" adr_nr="95" anmpos_nr="-1" doku_nr="1694351" gen="0" pos="12" preis_vk="2.4000" preis_lp="2.4000" druck="1" artikel_nr="601" artposarten_nr="1" />
  <row preis="330.0000" anzahl="1.0000" rabatt="0.0000" mwst="15.0000" bez="Abwasseranschluß" adoku_nr="500570" adr_nr="95" anmpos_nr="1" doku_nr="500570" gen="0" pos="6" preis_vk="330.0000" preis_lp="330.0000" druck="1" artikel_nr="304" artposarten_nr="1" />
</Data>

My goal is to have columns for adoku_nr (row id), attribute name, attribute value.

I achieved this through

select
  T.C.value('(../@adoku_nr)[1]', 'int') as ID,
  T.C.value('local-name(.)', 'nvarchar(128)') as Name,
  T.C.value('(.)[1]', 'nvarchar(max)') as Value
from @Data.nodes('/Data/row/@*') as T(C)

Result:

ID      Name    Value
1694351 preis   2.4000
1694351 anzahl  72.0000
1694351 rabatt  0.0000
1694351 mwst    16.0000
1694351 bez tägliche Standreinigung
1694351 adoku_nr    1694351
1694351 adr_nr  95
1694351 anmpos_nr   -1
1694351 doku_nr 1694351
1694351 gen 0
1694351 pos 12
1694351 preis_vk    2.4000
1694351 preis_lp    2.4000
1694351 druck   1
1694351 artikel_nr  601
1694351 artposarten_nr  1
500570  preis   330.0000
500570  anzahl  1.0000
500570  rabatt  0.0000
500570  mwst    15.0000
500570  bez Abwasseranschluß
500570  adoku_nr    500570
500570  adr_nr  95
500570  anmpos_nr   1
500570  doku_nr 500570
500570  gen 0
500570  pos 6
500570  preis_vk    330.0000
500570  preis_lp    330.0000
500570  druck   1
500570  artikel_nr  304
500570  artposarten_nr  1

Now my problem is that I execute this request in a larger xml, 120 lines in my case, and it becomes very slow (4 seconds), the problem looks like an identifier column, without returning back to the lower millisecond bracket, Is there a way Optimize this query to achieve the same goal, but with better performance?

Answer 1

You can include this need as XQueryyourXPath

Look at [@adoku_nr=sql:variable("@rowId")]

declare @data XML=
'<Data>
  <row preis="2.4000" anzahl="72.0000" rabatt="0.0000" mwst="16.0000" bez="tägliche Standreinigung" adoku_nr="1694351" adr_nr="95" anmpos_nr="-1" doku_nr="1694351" gen="0" pos="12" preis_vk="2.4000" preis_lp="2.4000" druck="1" artikel_nr="601" artposarten_nr="1" />
  <row preis="999" anzahl="999" rabatt="9999" mwst="16.0000" bez="tägliche Standreinigung" adoku_nr="999" adr_nr="95" anmpos_nr="-1" doku_nr="1694351" gen="0" pos="12" preis_vk="2.4000" preis_lp="2.4000" druck="1" artikel_nr="601" artposarten_nr="1" />
</Data>';

declare @rowId varchar(100)='1694351'; --try with "999" here to get the other row
select
  T.C.value('(../@adoku_nr)[1]', 'int') as ID,
  T.C.value('local-name(.)', 'nvarchar(128)') as Name,
  T.C.value('(.)[1]', 'nvarchar(max)') as Value
from @Data.nodes('/Data/row[@adoku_nr=sql:variable("@rowId")]/@*') as T(C)

.nodes() a CROSS APPLY r.nodes() , . ...

, :

select
  r.value('@adoku_nr', 'int') as ID,
  a.value('local-name(.)', 'nvarchar(128)') as Name,
  a.value('.', 'nvarchar(max)') as Value
from @Data.nodes('/Data/row') as A(r)
cross apply r.nodes('@*') AS B(a)