How to output some XML optional elements/nodes always using XQUERY

Question

I have a requirement where I need to output optional XML elements always (irrespective of data availability) , while writing into a .csv file.

XML File Format

<entries> 
  <countryCode>123</countryCode>  
  <adminDataMap> 
    <testAccount>True</testAccount>  
    <code>11</code> 
  </adminDataMap>  
  <privateMap> 
    <email>abc@abc.com</email>  
    <cancelReason>Not interested</cancelReason> 
  </privateMap> 
</entries>

Here 'entries' Root element 'cancelReason', 'testAccount' are optional elements which will only available once data is there.

i.e Some records will have those 'optional' fields and others not.

I have written a XQuery to extract all these elements

let $entries := /root/entries
return
    for $entry in $entries
    return
    <entries>
    {
        $entry/*,
        $entry/privateMap/email,
        $entry/privateMap/cancelReason,
        $entry/adminDataMap/code,
        $entry/adminDataMap/testAccount
    }
    </entries>

My requirement is to extract all mandatory and optional elements as OUTPUT. i.e Output Metadata always remain same. If optional 'cancelReason' doesn't have a value, it will be populated as blanks/nulls [something like that]

Please advise of any options in XQUERY which can be written so as to achieve this.

Vinod · Answer 1 · 2016-06-29T11:14:45.133

Try this Xquery:

let $entries := $root/entries

for $entry in $entries

return <OUTPUT>
{<mandatory>{$entry//countryCode}
{<adminDataMap>{$entry//adminDataMap/code}</adminDataMap>}
{$entry//privateMap/email}</mandatory>}
{<optional>{<testAccount>{data($entry/adminDataMap/testAccount)}</testAccount>}
<cancelReason>{data($entry/privateMap/cancelReason)}</cancelReason></optional>
}</OUTPUT>

that gives you mandatory and optional elements separately for every entry.

Alexander Petrov · Answer 2 · 2016-07-01T10:04:33.587

Try this XQuery:

for $entry in $entries
return
    <entries>
    {
        for $admin in $entry/adminDataMap
        return
            <admin>
                {$admin/code}
                <testAccount>{data($admin/testAccount)}</testAccount>
            </admin>,

        for $private in $entry/privateMap
        return
            <private>
                {$private/email}
                <cancelReason>{data($private/cancelReason)}</cancelReason>
            </private>
    }
    </entries>

This will give you blank elements for missing optional elements.

Well, try this

return
    <entries>
    {
        for $admin in $entries/adminDataMap
        return
            <admin>
                {$admin/code}
                <testAccount>{data($admin/testAccount)}</testAccount>
            </admin>,

        for $private in $entries/privateMap
        return
            <private>
                {$private/email}
                <cancelReason>{data($private/cancelReason)}</cancelReason>
            </private>
    }
    </entries>

I tested both methods on BaseX and SqlServer - both work.

I guess it all depends on how you set the initial value.
I see root in your code:

let $entries := /root/entries

Meanwhile, in xml there is no element with the root name.

This code works if the value is set as follows:

let $entries :=
<entries>
    <!-- ... -->
</entries>

I have tried your approach. I have seen that when I execute all three FOR loops (with variables 'entry','admin','private')only the elements/fields defined with 'entry' loop is getting output. other 2 FOR loops 'admin','private' are ignored. However, when I tried 'admin','private' FOR loops seperately,it's working like a charm. May be I am wrong with syntax. Not sure. Please advise. — Tom George, Jun 30 '16 at 22:10

How to output some XML optional elements/nodes always using XQUERY

2 Answers2