Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Help need for SQL Query to generate XML Value

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Sat Mar 08, 2008 9:36 am    Post subject: Help need for SQL Query to generate XML Value
Reply with quote

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:
Code:

SELECT XML2CLOB(                             
    XMLELEMENT(NAME "AdditionalMsg",         
    'TRD-ADTNLMSG'                           
    ))                                       
FROM SYSIBM.SYSDUMMY1       

ouput :
Code:
<AdditionalMsg>TRD-ADTNLMSG</AdditionalMsg>

Please help me on this.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 18, 2008 7:44 am    Post subject:
Reply with quote

Hello,

Quote:
we need to convert all the strings to sql statement.
What requirement dictates this?

I believe you need to continue with your current process.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Tue Mar 18, 2008 12:46 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Mar 18, 2008 5:38 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Wed Mar 19, 2008 11:27 am    Post subject: Reply to: Help need for SQL Query to generate XML Value
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 19, 2008 7:33 pm    Post subject:
Reply with quote

Hello,

Quote:
Instead of string we've decided to use SQL to construct XML message.
Again, what lead to this decision? Unless i misunderstand - even if you find some sql that will do what you want, would you not have 200 sql statements to maintain? How is this "better" than what is already working? It will almost surely require more machine resources than STRING. . .

Once we better understand your reqyuirement, we may be able to offer better suggestions.
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Thu Mar 20, 2008 4:06 pm    Post subject:
Reply with quote

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 icon_confused.gif . Please advice me is any other way to achieve this if not possible in sql.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Mar 20, 2008 5:44 pm    Post subject:
Reply with quote

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...
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Thu Mar 20, 2008 6:55 pm    Post subject:
Reply with quote

Hi Steve
Thanks for your suggestion icon_smile.gif . 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 icon_cry.gif .
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Mar 20, 2008 7:51 pm    Post subject:
Reply with quote

Hello,

Quote:
But we want to reduce the program length.
Why is the program length a concern?

I suspect that the linked executable code is not "too big" and the goal is to reduce the number of source statements.

FWIW, i believe it is better to have more lines of easily understood and maintainable code . . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Generate public-private key in batch ... vasanthz All Other Mainframe Topics 3 Fri Sep 15, 2017 9:20 am
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us