0
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

Tom George
  • 29
  • 1
  • 1
  • 7
  • Entries is plural and would make me assume that the XML Payload could include more than one entry. If so, please give a more complete example. Also, is it possible for the useCountIds value to be repeated more than once for an entry? If so, show the duplicates or not? – David Ennis -CleverLlamas.com Apr 05 '17 at 08:34

1 Answers1

0

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

har07
  • 88,338
  • 12
  • 84
  • 137
  • Hi har07 , I have tried this. Unfortunately what's happening is Entire set of 'UseCountIds' is being attached to a single 'id' value. i.e. a total of 100 'UseCountIds' are present. Every 'id' value , 100 'UseCountIds' are returned. – Tom George Apr 05 '17 at 06:27
  • Can post how the XQuery output should be, so we don't have to know how you convert the result of the XQuery into CSV? – har07 Apr 05 '17 at 06:31
  • with X Query which I used (mentioned in Q), I am getting output as below 1 100 200 300 If you see this, 'UseCountIds' are being combined into a single element node. This is because same element name but with different values. 'UseCountIds' is an optional column. It wont be available with all 'id' . I have used 'data() ' is to get a blank value even though 'UseCountIds' is not present for particular 'id'. Likewise, I will get all 'id' records irrespective of value present in 'UseCountIds'. – Tom George Apr 05 '17 at 07:12
  • 1
    CSV conversion is done by a utility which expects the input to be as 1 100 200 300 – Tom George Apr 05 '17 at 07:15
  • The initial XQuery I posted should've [returned result in such format](http://www.xpathtester.com/xquery/276a8298e3398008483c03ee288242ca), only it returned nothing in case of `entries` without any `UseCountIds` child.. check the updated answer – har07 Apr 05 '17 at 07:55
  • Since you've never done it so far: please consider [accepting answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) that solve the problem in question. Thanks – har07 Apr 05 '17 at 10:31