July 15, 2016 at 3:21 am
Hello all,
We make use of xml as a means of passing through lists of IDs to stored procedures as criteria.
The xml structure would always just have one column value being a distinct list of IDs.
The initial approach was making use of attribute based values and this was later switched to element based values.
Looking at the execution plan of a few different approaches of extracting the ID values, it looks to me like using attribute based xml is the most efficient.
I noticed that the execution plan for retrieving values from the attribute based xml fragment seems to be less expensive and makes use of less xml readers and incorporates filters when using the readers.
in the example, the attribute based xml makes use of 2 readers and the element based makes use of 2 when using the query method to extract the value and 3 when making use of the value method.
Is there some metric I am missing or a more efficient way to extract these values from the xml variable?
DECLARE @IDs XML, @IDs2 XML
SELECT @IDs =
(
'<IDs>
<ID>1</ID>
<ID>2</ID>
<ID>3</ID>
<ID>4</ID>
<ID>5</ID>
<ID>6</ID>
<ID>7</ID>
<ID>8</ID>
<ID>9</ID>
<ID>10</ID>
</IDs>'
), @IDs2 =
(
'<IDs>
<IDs ID="1" />
<IDs ID="2" />
<IDs ID="3" />
<IDs ID="4" />
<IDs ID="5" />
<IDs ID="6" />
<IDs ID="7" />
<IDs ID="8" />
<IDs ID="9" />
<IDs ID="10" />
</IDs>'
)
SET NOCOUNT ON
SELECT ids.DataSet.value('@ID[1]', 'int') AS ID
FROM @IDs2.nodes('/IDs/IDs') AS ids(DataSet)
SELECT ids.DataSet.query('./text()') AS ID
FROM @IDs.nodes('/IDs/ID') AS ids(DataSet)
SELECT ids.DataSet.value('.', 'int') AS ID
FROM @IDs.nodes('/IDs/ID') AS ids(DataSet)
any ideas/suggestions?
Thanks!
July 15, 2016 at 4:00 am
Quick suggestion
😎
SELECT ids.DataSet.value('(./text())[1]','INT') AS ID
FROM @IDs.nodes('/IDs/ID') AS ids(DataSet)
July 15, 2016 at 4:10 am
That is what I was looking for!
Need to work on my XQuery knowledge.
thanks for the help 😉
July 15, 2016 at 4:20 am
You are very welcome.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply