Been trying to write an xquery code that converts a flat xml to 5-level hierarchy xml file

Question

Would like to convert the following flax xml file to 5-level hierarchy xml structure using xquery, so far the all the xquery code i have written did not work.

<data>
<row>
    <Year>1999</Year>
    <Quarter>8</Quarter>
    <Month>5</Month>
    <Week>10</Week>
    <Flight>6/11/1995</Flight>
    <Un>WN</Un>
    <Air>193</Air>

</row>
<data>

Out result i would like:

<data>
   <row>
     <Year>
            <value>1999</value>
            <Quarter>
                <value>8</value>
                <Month>
                    <value>5</value>
                    <Week>10</Week>
                    <Flight>6/11/1995</Flight>
                    <Un>WN</Un>
                    <Air>193</Air>
                </Month>
            </Quarter>
        </Year>
    </row>
<data>

at other RDBMS, would just like to use xquery to be able to that. — JamesBond, Sep 06 '13 at 17:20
It would be helpful if you could provide more detail on how the schema handles multiple pieces of data. Only then will it be clear if a simple solution exists, or if you need to join data like the solution proposed by @JensErat. — wst, Sep 06 '13 at 19:23

wst · Accepted Answer · 2013-09-06T19:24:20.167

It's unclear what XQuery processor you're using, or the exact schema of the data you will need to process, but here is an example of how to transform the data, assuming each row contains a unique set of entries:

let $data :=
    <data>
    <row>
        <Year>1999</Year>
        <Quarter>8</Quarter>
        <Month>5</Month>
        <Week>10</Week>
        <Flight>6/11/1995</Flight>
        <Un>WN</Un>
        <Air>193</Air>    
    </row>
    </data>    
for $row in $data/row
return 
    element row {
      element Year {
        element value { $row/Year/data() },
        element Quarter {
          element value { $row/Quarter/data() },
          element Month {
            element value { $row/Month/data() },
            $row/Week,
            $row/Flight,
            $row/Un,
            $row/Air
          }
        }
      }
    }

score 0 · Answer 2 · answered Sep 06 '13 at 19:09

If you want a single element for each year/quarter/month, use this code:

<data>
  <row>{
    for $year in //row/Year/data()
    return
      <Year>{
        <value>{ $year }</value>,
        for $quarter in //row[Year=$year]/Quarter/data()
          return
            <Quarter>{
              <value>{ $quarter }</value>,
              for $row in //row[Year=$year and Quarter=$quarter]
              return
                <Month>{
                  <value>{ Month/data() }</value>,
                  $row/*[not(local-name(.) = ('Month', 'Quarter', 'Year'))]
                }</Month>
            }</Quarter>
      }</Year>
  }</row>
</data>

Been trying to write an xquery code that converts a flat xml to 5-level hierarchy xml file

2 Answers2