| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Mapping XML Array to Database tables |
« View previous topic :: View next topic » |
| Author |
Message
|
| wdwpsu |
 Posted: Wed Aug 20, 2003 11:29 am Post subject: Mapping XML Array to Database tables |
 |
|
Newbie
Joined: 20 Aug 2003
Posts: 6
|
I am trying to use a data table to help with the translation of data and everything I am trying seems to be backfiring.
XML looks like:
<DemographicData>
<FieldId>City</FieldId>
<FieldValue>Philadelphia</FieldValue>
<FieldId>State</FieldId>
<FieldValue>Pennsylvania</FieldValue>
</DemographicData>
We have a Demographic Table that maps these fields:
ie.
DemoName DemoValue
Address1 Field12
City Field13
State Field14
Our translated message would look like:
<DemographicData>
<FieldId>Field13</FieldId>
<FieldValue>Philadelphia</FieldValue>
<FieldId>Field14</FieldId>
<FieldValue>Pennsylvania</FieldValue>
</DemographicData>
I did the following:
SET Environment.Variables.Demographic[] =
(SELECT A.DemoName, A.DemoValue
FROM Demographic);
to grab the information from the database once. (Efficiency is key here)
And then I performed the following select to map the data
Set OutputRoot.XML.DemographicData[] =
(SELECT A.DemoValue AS FieldId, B.FieldValue AS FieldValue
from Environment.Variables.Demographic[] as A,
InputRoot.XML.DemographicData[] as B
where A.DemoName=B.FieldValue
);
This works for the very first row in the table, but that's it. And, performance is subpar at best.
Thanks for any suggestions. |
|
| Back to top |
|
 |
| Craig B |
| Posted: Wed Aug 20, 2003 11:44 pm Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
In your FROM clause of your SELECT statement you have specified "InputRoot.XML.DemographicData[] as B", then reference B.FieldValue in your fields, and in the Where clause. This will want to interate over each instance of DemographicData, and then find a field called fieldValue with it. From what you have described, dont you want to iterate over each fieldValue within DemographicData. If you are expecting multiple DemographicData records, then I cannot see how this would get past the XML parser because this would mean you would have multiple root elements (since this record is the root element of the XML message), which is not allowed.
If you are not getting any XML errors, then this would suggest you have one DemographicData record, and this will mean only one instance will be iterated over. This may explain why this only gives you one row in your output.
It should also be noted that you are writing your results into a repeating DemographicData record. If this had written multiple records, then this would have failed on writing of the XML message on an output node, since this would have multiple root elements as well.
You could try the following, if you do wish to iterate over FieldValue fields.
| Code: |
Set OutputRoot.XML.someRoot.DemographicData[] =
(SELECT A.DemoValue AS FieldId, B AS FieldValue
from Environment.Variables.Demographic[] as A,
InputRoot.XML.DemographicData.FieldValue[] as B
where A.DemoName=B
);
|
_________________
Regards
Craig |
|
| Back to top |
|
|
| wdwpsu |
| Posted: Thu Aug 21, 2003 5:28 am Post subject: |
|
|
Newbie
Joined: 20 Aug 2003
Posts: 6
|
A correction to my original post. My corrected secondary query is:
And then I performed the following select to map the data
Set OutputRoot.XML.DemographicData[] =
(SELECT A.DemoValue AS FieldId, B.FieldValue AS FieldValue
from Environment.Variables.Demographic[] as A,
InputRoot.XML.DemographicData[] as B
where A.DemoName=B.FieldId
); |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
