| Author |
Message
|
| klabran |
 Posted: Wed Apr 28, 2004 1:15 pm Post subject: Combine two XML incoming repeatable tags into one |
 |
|
Master
Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ
|
I have an incoming XML record like...
<PersonPhysicalFeature>
<PersonPhysicalFeature_ROW>
<PhysicalFeatureCategoryText>TATT</PhysicalFeatureCategoryText>
<PhysicalFeatureLocationText>UPP CHES</PhysicalFeatureLocationText>
<PhysicalFeatureDescriptionText>"USMC"</PhysicalFeatureDescriptionText>
</PersonPhysicalFeature_ROW>
</PersonPhysicalFeature>
<OtherPersonPhysicalCharacter>
<OtherPersonPhysicalCharacter_ROW>
<PhysicalFeatureCategoryText>FACIAL HAIR</PhysicalFeatureCategoryText>
<PhysicalFeatureLocationText>FACE</PhysicalFeatureLocationText>
<PhysicalFeatureDescriptionText>BEARD</PhysicalFeatureDescriptionText>
</OtherPersonPhysicalCharacter_ROW>
</OtherPersonPhysicalCharacter>
I need it to end up like....
<PersonPhysicalFeature>
BLAH.....
</PersonPhysicalFeature>
<PersonPhysicalFeature>
BLAH.....
</PersonPhysicalFeature>
SET OutputRoot.XML.Arrest.ActivitySuspect.PersonPhysicalDetails.PersonPhysicalFeature[]=(SELECT R.PhysicalFeatureCategoryText,R.PhysicalFeatureLocationText,R.PhysicalFeatureDescriptionText FROM InputBody.Arrests.Arrest[I].PersonPhysicalFeature.PersonPhysicalFeature_ROW[] AS R);
SET OutputRoot.XML.Arrest.ActivitySuspect.PersonPhysicalDetails.PersonPhysicalFeature[]=(SELECT R.PhysicalFeatureCategoryText,R.PhysicalFeatureLocationText,R.PhysicalFeatureDescriptionText FROM InputBody.Arrests.Arrest[I].OtherPersonPhysicalCharacter.OtherPersonPhysicalCharacter_ROW[] AS R);
Problem with above code is that the second set would overwrite the first set right? ESQL doesn't support UNIONS so what are my alternatives for this? |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Apr 28, 2004 1:23 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Detach and attach the proper tree.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| klabran |
| Posted: Wed Apr 28, 2004 2:22 pm Post subject: |
|
|
Master
Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ
|
How would I get around dropping the *_ROW from each Other Character row using detach and attach?
I was thinking something like...
SET OutputRoot.XML.Arrest.ActivitySuspect.PersonPhysicalDetails.OtherPersonPhysicalFeature[]=(SELECT R.PhysicalFeatureCategoryText,R.PhysicalFeatureLocationText,R.PhysicalFeatureDescriptionText FROM InputBody.Arrests.Arrest[I].OtherPersonPhysicalCharacter.OtherPersonPhysicalCharacter_ROW[] AS R);
DECLARE OtherRef REFERENCE = OutputRoot.XML.Arrest.ActivitySuspect.PersonPhysicalDetails.OtherPersonPhysicalFeature[];
DETACH OtherRef;
ATTACH OtherRef TO OutputRoot.XML.Arrest.PersonPhysicalDetails.PersonPhysicalFeature[] AS LASTCHILD;
???? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Apr 28, 2004 2:44 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You could also use Insert.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| klabran |
| Posted: Wed Apr 28, 2004 3:31 pm Post subject: |
|
|
Master
Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ
|
| Can't seem to find info on insert except for database inserts... |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Apr 28, 2004 4:01 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
It works just as well on message trees.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| klabran |
| Posted: Wed Apr 28, 2004 4:11 pm Post subject: |
|
|
Master
Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ
|
I am getting a compile error....
Complaining that it expects database.table...
This is my test that caused the error...
Insert into outputroot.arrest (Test,Test1) values('t','t1');
kevin |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Apr 28, 2004 4:54 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Hrm. 
It looks like it doesn't work on message trees in v5...
You should be able to nest Selects to do what you want, I think.
Or you could always just loop over the children of both and do a manual join.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| klabran |
| Posted: Thu Apr 29, 2004 6:05 am Post subject: |
|
|
Master
Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ
|
How would I do a nested select to solve this....
The nested selects I see in the esql 2.1 manual all show that it would nest another tree instead of just another personphysicalfeature?
I was hoping to avoid the loop through all the records and manually add another personphysicalfeatue node....  |
|
| Back to top |
|
|
| klabran |
| Posted: Thu Apr 29, 2004 7:24 am Post subject: |
|
|
Master
Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ
|
Well here's my breakdown of my attempts and what I have currently working...
Working but I hope there is a better way....????
SET OutputRoot.XML.Arrest.ActivitySuspect.PersonPhysicalDetails.PersonPhysicalFeature[]=(SELECT R.PhysicalFeatureCategoryText,R.PhysicalFeatureLocationText,R.PhysicalFeatureDescriptionText FROM InputBody.Arrests.Arrest[I].PersonPhysicalFeature.PersonPhysicalFeature_ROW[] AS R);
DECLARE CNT INTEGER;
DECLARE EXISTFEATURE INTEGER;
SET EXISTFEATURE= (SELECT COUNT(*) FROM OutputRoot.XML.Arrest.ActivitySuspect.PersonPhysicalDetails.PersonPhysicalFeature[]);
SET CNT = 1;
--Combine any Other Features
WHILE CNT <= (SELECT COUNT(*) FROM InputBody.Arrests.Arrest[I].OtherPersonPhysicalCharacter.OtherPersonPhysicalCharacter_ROW[] AS OtherNum) DO
SET OutputRoot.XML.Arrest.ActivitySuspect.PersonPhysicalDetails.PersonPhysicalFeature[EXISTFEATURE + CNT]=(SELECT R.PhysicalFeatureCategoryText,R.PhysicalFeatureLocationText,R.PhysicalFeatureDescriptionText FROM InputBody.Arrests.Arrest[I].OtherPersonPhysicalCharacter.OtherPersonPhysicalCharacter_ROW[CNT] AS R);
SET CNT = CNT + 1;
END WHILE;
Attempted....
DETACH/ATTACH
Result -> Still had the issue where the OtherPersonPhysicalFeature tag was in the message tree. I would have to do additional work to remove this... Yuck!
INSERT
Result -> Recieved compile error. Seems in MQSI 2.1 we can't do INSERT statements on a message tree only DB's.
Nested Selects
Result -> Never tried because samples in ESQL 2.1 ref. seemed to show me that it would not give me the desired result.
I posted this only because I am a rookie at MQSI but not to programming. I want to learn several techniques for solving a problem to help discover which techniques work best where. Hopefully, others will learn from this also.
Thanks for all your help jefflowrey! |
|
| Back to top |
|
|
|
|
