| Author |
Message
|
| jfrankman |
 Posted: Thu Jun 18, 2009 2:41 pm Post subject: Best way to Convert Flat SQL Query to XML |
 |
|
Apprentice
Joined: 18 Jun 2009
Posts: 36
|
Can someone tell me how to map a flat database select result to a hierarchical xml?
Suppose I have a select statement like:
select orderid as OrderId, linedescription as LineDesc, lineamount as LineAmount
from Database.orders as A, database.lineitems as B
where A.orderid = B.Orderid
And I want to produce XML like:
| Code: |
<Orders>
<Order>
<OrderID>1</OrderID>
<Line>
<LineDesc>xFoo</LineDesc>
<LineAmount>50.00</LineAmount>
</Line>
<Line>
<LineDesc>xBar</LineDesc>
<LineAmount>25.00</LineAmount>
</Line>
</Order>
<Order>
<OrderID>5</OrderID>
<Line>
<LineDesc>Foo</LineDesc>
<LineAmount>50.00</LineAmount>
</Line>
<Line>
<LineDesc>Bar</LineDesc>
<LineAmount>50.00</LineAmount>
</Line>
</Order>
</Orders> |
Is there a way to do this with a single Select statement or do I need to resort to control breaks and for loops? |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Jun 18, 2009 11:33 pm Post subject: Re: Best way to Convert Flat SQL Query to XML |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| jfrankman wrote: |
| do I need to resort to control breaks and for loops? |
But 1 loop & no control breaks that I can see.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jun 19, 2009 1:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
...
SET OutputRoot.XMLNSC.Orders.Order[] = select...
... ? |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jun 19, 2009 1:42 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
SET OutputRoot.XMLNSC.Orders.Order[] = select...
|
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jun 19, 2009 1:44 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I'm sure I'm missing something.
There's maybe some complicated stuff needed to create Line.LineDesc, but... |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Jun 19, 2009 3:42 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Jeff is right as usual 
ESQL Select can build trees. So your ESQL select will look something like (not tested):
| Code: |
SET OutputRoot.XMLNSC.Orders.Order[] = SELECT A.orderid as OrderId, B.linedescription as Line.LineDesc, B.lineamount as Line.LineAmount
FROM Database.orders as A, database.lineitems as B
where A.orderid = B.Orderid; |
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jun 19, 2009 3:44 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mgk wrote: |
| Jeff is right as usual |
I find my lack of faith disturbing....
Darth Vitor
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jfrankman |
| Posted: Fri Jun 19, 2009 6:30 am Post subject: |
|
|
Apprentice
Joined: 18 Jun 2009
Posts: 36
|
Thanks for the help. I have a followup question. In the example given, the orders data would be repeated in the sql query:
orderid LineItem
1 1
1 2
1 3
2 1
2 2
Given the above Message broker will not repeat the order element in the xml. Is this correct? I want this to happen:
<Orders>
<Order>
<ID>1</ID>
<LineItems>
<LineItem>1</LineItem>
<LineItem>2</LineItem>
</LineItems>
</Order>
</Orders>
I was afraid it would do something like:
<Orders>
<Order>
<ID>1</ID>
<LineItems>
<LineItem>1</LineItem>
</LineItems>
</Order>
<Order>
<ID>1</ID>
<LineItems>
<LineItem>2</LineItem>
</LineItems>
</Order>
</Orders> |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jun 19, 2009 6:49 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| jfrankman wrote: |
I want this to happen:
<Orders>
<Order>
<ID>1</ID>
<LineItems>
<LineItem>1</LineItem>
<LineItem>2</LineItem>
</LineItems>
</Order>
</Orders> |
That's entirely contradictory to your original example...
?
| jfrankman wrote: |
I was afraid it would do something like:
<Orders>
<Order>
<ID>1</ID>
<LineItems>
<LineItem>1</LineItem>
</LineItems>
</Order>
<Order>
<ID>1</ID>
<LineItems>
<LineItem>2</LineItem>
</LineItems>
</Order>
</Orders> |
Yes, that's what it will do.
Because that's what you said you wanted.
If you want one <Orders> per Row, you can adjust it and Set OutputRoot.XMLNSC.Message.Orders[] = ...
(Message added so you properly have a single rooted XML document)
If you want one Orders per OutputRoot.XMLNSC, you will need a loop. |
|
| Back to top |
|
|
| jfrankman |
| Posted: Fri Jun 19, 2009 12:05 pm Post subject: |
|
|
Apprentice
Joined: 18 Jun 2009
Posts: 36
|
The original example had one order element per unique order record. I was afraid that an order element would get created for each row in the result set even if the order id was the same. The different between my original example and the one I just gave is that the second example repeats order elements.
So, given an orderid="1" with ten line items I would want to see ONE order element with with TEN line item elements. I would not want to see ten order elements with a single line item in each. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Jun 19, 2009 9:52 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
As mqjeff indicated you will need a loop along with a CARDINALITY statement to assemble the output in the way you want. This is not difficult ESQL. If you are not sure how to do this then you should invest in some formal training in Broker Development.
Please try it for yourself and then come back with what you have done (ie the code) and where it is going wrong.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Sat Jun 20, 2009 12:31 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| smdavies99 wrote: |
| As mqjeff indicated you will need a loop along with a CARDINALITY statement to assemble the output in the way you want. |
Or other (potentially more efficient) looping mechanism.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Jun 21, 2009 7:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Or modify the select, possibly with an inner select, to group the lines. |
|
| Back to top |
|
|
|
|
