Help need for SQL Query to generate XML Value

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

Hi,
I need one help to generate XML values using Sql query means the following xml data should be populated by sql query. Currently this data is build using string verb on my cobol program and we need to convert all the strings to sql statement.

XMLData
---------
<env:Envelope xmlns:rm="sample data" xmlns:mtc="input values from cobol" xmlns:env="url-link here" xmlns:fpml="http-url-link" xmlns:xsi="http-url-link" xsi:schemaLocation="input data"></env:Envelop>

Note: the tag input values are hard coded values in cobol program and it is not getting from any table.
Sample sql:

dick scherrer · Posted: Tue Mar 18, 2008 7:44 am

Hello,

ksk · Posted: Tue Mar 18, 2008 12:46 pm

I don't think Mainframes and XML interacts directly and the query can be written as you mentioned. There should be some interface like MQ series to connect Mainframes and the tool you are using for XML.

KSK

stodolas · Posted: Tue Mar 18, 2008 5:38 pm

Of course the mainframe can do XML. Both DB2 and Cobol can generate XML directly. MQ doesn't do any translation to/from an XML doc.

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

Hi KSK,
I agree with your statement , we have an interface MQ series which will have the XML data on the input queue to the respective program.
Let me clear about the process. User can upload some trade information via web like CSV format and that will be rooted to our mainframe system via MQ series which will have input values as XML format.

Now, we have to parse that XML input data from the queue and build XML message with some business logic and then the final XML message would be stored in to DB2 table. The final output of the XML message is more than 32K. My requirement is, in our program the whole message is built using string in COBOL, so it has more than 200 strings and we want to get rid of these strings. Instead of string we've decided to use SQL to construct XML message. The previous example which I had posted for one of the string.
Hope I explained clear now

dick scherrer · Posted: Wed Mar 19, 2008 7:33 pm

Hello,

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

Hi Sch
Your understanding is correct. We need roughly 200 SQL statements to replace all the strings or partially, but we are trying to make everything in to single or more than one query to build the XML message. Also, the entire strings are not placed in one program and there are some sub programs which would build some of the XML tags out of 200 strings.

I knew this approach will consume more machine resources when compare to strings. But we want to reduce the program length. So we've just come up with this approach

. Please advice me is any other way to achieve this if not possible in sql.

stodolas · Posted: Thu Mar 20, 2008 5:44 pm

You could create a copybook of what you need and use the COBOL keyword XML GENERATE and it will magically create the XML for you...

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

Hi Steve
Thanks for your suggestion

. Actually, we initially had tried the XML generate approach before enter into this sql and able to covert some part of tags. But we failed in that operation since the complexity of the XML data length and each tag builds based on some condition and not able to defining layout for entire XML message structure

.

dick scherrer · Posted: Thu Mar 20, 2008 7:51 pm

Hello,