Return all possible values from xQuery

Question

I have the below code which returns repair costs if I manually select the specific instances e.g.[1],[2] etc.

I wanted to know if it was possible to return all items dynamically

    select
        *
        ,Data1.Data.value('(/PurchaseModel//DamageModel/Damage/RepairCost)[1]', 'Float')
        ,Data1.Data.value('(/PurchaseModel//DamageModel/Damage/RepairCost)[2]', 'Float')
        ,Data1.Data.value('(/PurchaseModel//DamageModel/Damage/RepairCost)[3]', 'Float')
        ,Data1.Data.value('(/PurchaseModel//DamageModel/Damage/RepairCost)[4]', 'Float')
        ,Data1.Data.value('(/PurchaseModel//DamageModel/Damage/RepairCost)[5]', 'Float')
        ,Data1.Data.value('(/PurchaseModel//DamageModel/Damage/RepairCost)[6]', 'Float')
        ,Data1.Data.value('(/PurchaseModel//DamageModel/Damage/RepairCost)[7]', 'Float')
    FROM
        #Data

You might want to consider giving users some sample xml to work with here. — Thom A, Feb 20 '18 at 11:51

score 3 · Answer 1 · answered Feb 21 '18 at 07:12

3

You have to use dynamic SQL, here is how to construct such query once and then generate it automatically:

--you can omit declarations of these integers if you know limits of an index
declare @start int, @end int, @query nvarchar(1000)
select @start=1, @end=7, @query = 'select *'
--generate indexes that you want to retrieve
;with cte as (
  select @start [n]
  union all
  select [n] + 1 from cte where n < @end
)
--construction of actual query, that you want to execute
select @query = @query + replace(', Data1.Data.value(''(/PurchaseModel//DamageModel/Damage/RepairCost)[@index]'', ''Float'')', '@index', cast(n as nvarchar(3))) from cte

sp_executesql @query + N' from #Data'

answered Feb 21 '18 at 07:12

Michał Turczyn

32,028
14
47
69

Hi Michał, I'm sorry I wasn't very clear, I wanted to also be able to somehow capture the no of instances as each different row can have a different no of elements, so you currently run 1-7 but I'd want to be able to run 1 to n – Krishn Feb 22 '18 at 10:15
@Krishn Well, take maximum of these numbers and assing it to `@end` variable. Done :) – Michał Turczyn Feb 22 '18 at 10:46

score 0 · Answer 2 · answered Feb 24 '18 at 09:20

You can use the xml methods nodes and value like this:

DECLARE @xml xml =
'<PurchaseModel>
    <DamageModel>
        <Damage>
            <RepairCost>100.00</RepairCost>
            <RepairCost>200.00</RepairCost>
            <RepairCost>300.00</RepairCost>
            <RepairCost>400.00</RepairCost>
            <RepairCost>500.00</RepairCost>
            <RepairCost>600.00</RepairCost>
            <RepairCost>700.00</RepairCost>
            <RepairCost>800.00</RepairCost>
            <RepairCost>900.00</RepairCost>
            <RepairCost>1000.00</RepairCost>
        </Damage>
    </DamageModel>
</PurchaseModel>'


select row.value('.','float') as repaircost
from @xml.nodes('(/PurchaseModel//DamageModel/Damage)') a(damage)
cross apply damage.nodes('RepairCost') bar(row)

This returns:

repaircost
----------
100
200
300
400
500
600
700
800
900
1000

score 0 · Answer 3 · answered Feb 26 '18 at 10:15

With the following code you will have a Select command with all the values of the XML file. You could just insert it into some table afterwards.

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT RankID,RankName,RankShort,@id
FROM OPENXML(@hDoc, '/PurchaseModel//DamageModel/Damage')
WITH (  RepairCost int 'RepairCost ')

EXEC sp_xml_removedocument @hDoc

Return all possible values from xQuery

3 Answers3