title: The best way to extract data from xml with xquery
body: <p>Consider the following xml:</p>
  
  <pre><code>&lt;Persons num="3"&gt;
    &lt;Person age="5" /&gt;
    &lt;Person age="19" /&gt;
  &lt;/Persons&gt;
  </code></pre>
  
  <p>There is a need to extract this xml into a relational table:</p>
  
  <pre><code>Persons table (Age1 int, Age2 int, Age3 int , Age4 int)
  </code></pre>
  
  <p>Parsing has to satisfy the following constraints:</p>
  
  <ul>
  <li>all persons with age >=18 must be assigned to columns with smallest column number and the value has to be 18</li>
  <li>if the age of the person is not given it is equal to 18</li>
  <li>all persons with age &lt;18 must follow</li>
  <li>if there are less than 4 persons, those which are not provided must have age=-1</li>
  </ul>
  
  <p>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:</p>
  
  <pre><code>18 18 5 -1
  </code></pre>
  
  <p>Is there the best way to do so with xpath?</p>
  
  <p>Till now I can parse the xml and assign ages but what is not clear is to how make ordering:</p>
  
  <pre><code>declare @XmlData xml = 
  '&lt;Persons num="3"&gt;
      &lt;Person age="5" /&gt;
      &lt;Person age="19" /&gt;
  &lt;/Persons&gt;'
  
  declare @Persons table (Age1 int, Age2 int, Age3 int , Age4 int)
  insert into @Persons (Age1, Age2, Age3, Age4)
  select ISNULL(Age1, case when Num&gt;= 1 then 18 else -1 end) Age1
      , ISNULL(Age2, case when Num&gt;= 2 then 18 else -1 end) Age2
      , ISNULL(Age3, case when Num&gt;= 3 then 18 else -1 end) Age3
      , ISNULL(Age4, case when Num&gt;= 4 then 18 else -1 end) Age4
  from (
      select Persons.Person.value('@num','smallint') as Num
            ,Persons.Person.value('Person[@age&lt;18][1]/@age','smallint') as Age1
            ,Persons.Person.value('Person[@age&lt;18][2]/@age','smallint') as Age2
            ,Persons.Person.value('Person[@age&lt;18][3]/@age','smallint') as Age3
            ,Persons.Person.value('Person[@age&lt;18][4]/@age','smallint') as Age4
      from @XmlData.nodes('/Persons') Persons(Person)
   ) Persons  
  
  select *
  from @Persons
  </code></pre>
  
  <p>Result is</p>
  
  <pre><code>5 18 18 -1
  </code></pre>