The best way to extract data from xml with xquery

Question

Consider the following xml:

<Persons num="3">
  <Person age="5" />
  <Person age="19" />
</Persons>

There is a need to extract this xml into a relational table:

Persons table (Age1 int, Age2 int, Age3 int , Age4 int)

Parsing has to satisfy the following constraints:

• all persons with age >=18 must be assigned to columns with smallest column number and the value has to be 18
• if the age of the person is not given it is equal to 18
• all persons with age <18 must follow
• if there are less than 4 persons, those which are not provided must have age=-1

In a given example, there are 3 persons, ages of 2 of them are provided: 5 and 19 respectively. The content of the table Persons has to be the following:

18 18 5 -1

Is there the best way to do so with xpath?

Till now I can parse the xml and assign ages but what is not clear is to how make ordering:

declare @XmlData xml = 
'<Persons num="3">
    <Person age="5" />
    <Person age="19" />
</Persons>'

declare @Persons table (Age1 int, Age2 int, Age3 int , Age4 int)
insert into @Persons (Age1, Age2, Age3, Age4)
select ISNULL(Age1, case when Num>= 1 then 18 else -1 end) Age1
    , ISNULL(Age2, case when Num>= 2 then 18 else -1 end) Age2
    , ISNULL(Age3, case when Num>= 3 then 18 else -1 end) Age3
    , ISNULL(Age4, case when Num>= 4 then 18 else -1 end) Age4
from (
    select Persons.Person.value('@num','smallint') as Num
          ,Persons.Person.value('Person[@age<18][1]/@age','smallint') as Age1
          ,Persons.Person.value('Person[@age<18][2]/@age','smallint') as Age2
          ,Persons.Person.value('Person[@age<18][3]/@age','smallint') as Age3
          ,Persons.Person.value('Person[@age<18][4]/@age','smallint') as Age4
    from @XmlData.nodes('/Persons') Persons(Person)
 ) Persons  

select *
from @Persons

Result is

5 18 18 -1

Answer 1

I have found a bit dirty solution:

select ISNULL(Age1, case when Num>= 1 then 18 else -1 end) Age1
    , ISNULL(Age2, case when Num>= 2 then 18 else -1 end) Age2
    , ISNULL(Age3, case when Num>= 3 then 18 else -1 end) Age3
    , ISNULL(Age4, case when Num>= 4 then 18 else -1 end) Age4
from (
    select Persons.Person.value('@num','smallint') as Num
          ,Persons.Person.value('xs:integer(fn:number(@num))+1','int') as Num1
          ,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))][1]/@age','smallint') as Age1
          ,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))-1][1]/@age','smallint') as Age2
          ,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))-2][1]/@age','smallint') as Age3
          ,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))-3][1]/@age','smallint') as Age4 
    from @XmlData.nodes('/Persons') Persons(Person)
 ) Persons

The idea of a solution is to first extract those contacts that are >=18, then extract those that are 0 < age < 18 and finally set those that are not provided to -1

UPD: despite the fact that solution provided correct results, its cost is high: ~1000 in estimated execution plan