| Author |
Message
|
| wendy |
 Posted: Fri Jul 01, 2005 4:54 am Post subject: Retrieve XML from a DB2 database |
 |
|
Apprentice
Joined: 10 Dec 2001
Posts: 47
|
I have successfuly stored an XML Message onto DB2 table.
However I need to get this data and resend the XML to a separate queue.
The select statement works but how do i output the data correctly.Below is the ESQL code:
SET Environment.Variable.StoredData =
The (SELECT A.USER ,A.EFFECTIVEDATE ,A.AGREEMENTNO ,A.DATA FROM Database.MQADMIN.SRS_COMPASSINSYNC AS A
WHERE (A.AGREEMENTNO = TRIM( "InputBody"."AgreementNumber")) and A.COMPLETEINDICATOR ='N') ;
IF (Environment.Variable.StoredData) is not null then
SET OutputRoot.XML.Test.(XML.AsisElementContent) = CAST( Environment.Variable.StoredData.DATA AS CHAR);
end if;
The above statements work but how do I remove the tag Test as the data in the coloumn DATA is a complete XML Message (sample below).
When I try
SET OutputRoot.XML.(XML.AsisElementContent) = CAST( Environment.Variable.StoredData.DATA AS CHAR);
end if;
It does not work.
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\Data\CompassInSync\Broadcast-new.xsd" systemId="String" applicationId="String" timeStamp="2001-12-17T09:30:47-05:00" userId="naa1801">
<CrfActionGroup/>
<COMMAND>
<AgreementBroadcastMessage>
<LibertyAgreementBroadcast paramNo="0">
<AgreementBroadcast id="ID000156" refid="ID000156" externalRefid="String" supertype="String">
<KeyGroup id="ID000157" refid="ID000157" keyGroupType="String">
<UUID>Text</UUID>
<AlternateId value="String" sourceLogicalId="String" attributeString="String" newValue="String">Text</AlternateId>
</KeyGroup>
<ActionType>Terminate</ActionType>
<LongProperty>
<kind>814647</kind>
<value>2147483647</value>
</LongProperty>
</AgreementBroadcast>
</LibertyAgreementBroadcast>
</AgreementBroadcastMessage>
</COMMAND>
</Message>
Please help. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri Jul 01, 2005 5:01 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You need to reparse the XML, using the Create Field statement with the Parse clause.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| frikkieo |
| Posted: Fri Jul 01, 2005 6:54 am Post subject: |
|
|
Novice
Joined: 16 Jul 2004
Posts: 13
Location: South Africa
|
Here is a sample of the Create Field statement Jeff referred to.
CREATE FIRSTCHILD OF Environment DOMAIN('XML') PARSE(CAST(Environment.RESULT.[1].XMLSection AS BLOB CCSID 437), InputProperties.Encoding, 437) ; |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Mon Jul 04, 2005 1:30 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Shouldn't 437 also be replaced by InputRoot.Properties.CCSID ?
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| EddieA |
| Posted: Mon Jul 04, 2005 9:39 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
| Quote: |
| Shouldn't 437 also be replaced by InputRoot.Properties.CCSID |
Actually, it should be replaced by whatever the CCSID was of the data that was stored.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
|
|
