MQSeries.net :: View topic - ESQL Help, SELECT if element missing first array

peterlaughter

Hi,
I am trying to come up with a SELECT query
SELECT MonitorRef FROM InputRoot.SOAP.Body.ns:invoke.Orders.Order[] AS OrderRef, OrderRef.Monitor[] AS MonitorRef

for the following input
<soapenv:Envelope xmlns:soapenv="ns deleted" xmlns:ord="ns deleted">
<soapenv:Header/>
<soapenv:Body>
<ord:invoke>
<Orders>
<Order>
<CPU>
<Desc>CPU 1</Desc>
</CPU>
<CPU>
<Desc>CPU 2</Desc>
</CPU>
</Order>
<Order>
<CPU>
<Desc>CPU 3</Desc>
</CPU>
<Monitor>
<Desc>Monitor 1</Desc>
</Monitor>
</Order>
<Order>
<Monitor>
<Desc>Monitor 2</Desc>
</Monitor>
<Monitor>
<Desc>Monitor 3</Desc>
</Monitor>
</Order>
</Orders>
</ord:invoke>
</soapenv:Body>
</soapenv:Envelope>
I was expecting the following output
<soapenv:Envelope xmlns:soapenv="ns deleted">
<soapenv:Body>
<NS1:invokeResponse xmlns:NS1="ns deleted">
<Orders>
<CPUs>
<Desc>CPU 1</Desc>
</CPUs>
<CPUs>
<Desc>CPU 2</Desc>
</CPUs>
<CPUs>
<Desc>CPU 3</Desc>
</CPUs>
<Monitor/>
<Monitor>
<Desc>Monitor 1</Desc>
</Monitor>
<Monitor>
<Desc>Monitor 2</Desc>
</Monitor>
<Monitor>
<Desc>Monitor 3</Desc>
</Monitor>
</Orders>
</NS1:invokeResponse>
</soapenv:Body>
</soapenv:Envelope>

But the Monitor elements are missing because there is no Monitor element in the first Orders element. When I add <Monitor/> to the first Orders element then the Output comes as expected, but the Monitor has 0,n multiplicity.

Please suggest me if I am missing something.

Thanks in advance