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 target is to have columns for adoku_nr(id of the row), name of attribute, attribute value.
I have achieved this via
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 if i execute this query on a larger xml, 120 Rows in my case, its getting super slow (4 seconds), the issue appear to be the ID Column, without it im back to the lower millisecond bracket. Is there a way to optimize this query to achieve the same goal but with better performance?
You can include this need as XQuery into your XPath
Look at the [@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)
With a .nodes() on the rows and a CROSS APPLY r.nodes() for the attributes you are walking straightly downwards. No expensive backward navigation needed...
I assume you are looking for this:
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)