How to output XML node with same name[multiple occurrences] using an XQuery

Question

My XML Response Load appears as below


       <entries>
            <id>1</id>
            <UseCountIds>100</UseCountIds>
            <UseCountIds>200</UseCountIds>
            <UseCountIds>300</UseCountIds>
      </entries>

Here 'entries' is parent node which has child elements viz 'id', 'UseCountIds'. Above example is for id=1 . Likewise , there are many 'entries' for id=2 or id=3 etc.

'id' is kind of unique value.

I have done an XQuery to extract Child elements from XML Payload . Below is the Xquery used

             let $entries := /root/entries
             return
             for $entry in $entries
             return
             <entries>
              {
                $entry/id,
                <UseCountIds>{data($entry/UseCountIds)}</UseCountIds>
               }    
             </entries>

Problem is , with above XQuery output if it's load into .csv file, It is appearing as

                   id,UseCountIds
                   1,100 200 300

UseCountIds (Multiple values) are appearing in a column with space delimited.

My requirement is to have desired output like below

                   id,UseCountIds
                   1,100
                   1,200
                   1,300

Also, UseCountIds are not limited to only 3 occurrences. For a unique 'id' can have 'n' no. of UseCountIds .

It is always good to bring UseCountIds connected to a unique 'id' in row level.

Please share your thoughts how XQuery can be tweaked to get desired output mentioned above.

Thanks, T G

Answer 1

It isn't clear how you generate CSV from the output of the XQuery, but, if I can assume that you simply need one entries element for each UseCountIds in the source XML for it to produce the desired CSV data, then this is one possible XQuery :

for $c in /root/entries/UseCountIds
return
<entries>
{
    $c/preceding-sibling::id,
    $c
}    
</entries>

If didn't produce the desired output, then please post how the XQuery output should be.

---

To be able to return entries even when there is no child UseCountIds:

for $e in /root/entries
let $id := $e/id
let $useCountIds := $e/UseCountIds
return
    if($useCountIds) then
        for $c in $useCountIds
        return
            <entries>
            {
                $id,
                $c
            }
            </entries>
    else 
        <entries>
        {
            $id,
            <UseCountIds></UseCountIds>
        }
        </entries>

demo