Midrange News for the IBM i Community

Posted by: renojim
has no ratings.
Published: 20 Sep 2013
Revised: 20 Sep 2013 - 3893 days ago
Last viewed on: 17 May 2024 (4424 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL - XML Published by: renojim on 20 Sep 2013 view comments(1)

So to at least break the silence, I was asked today if I could insert xml via sql given a properly designed table, such as this in RPG:

insert into test.table (XMLVAL)                        
1</V2></PMT><PMT><V1>-50</V1><V2>test 2</V2></PMT></test>')

...and I said 'sure' - because I always do the dumbest thing possible. And I did it in an interactive session, no problem.

I've read a bit about this, but never done it, so I was not surprised when I did a SELECT * on the table, I got:


1        *pointer

2        *pointer

...and I'm fine with that, but the question is how do I extract the values? According to what I've read, the type of SELECT I'm accustomed to should work, so I'm expecting, if I do:


that I would get 123456789.

but I get an error 'Column or Global Variable V1 not found'. In fact, I can't get any statement to work, other than SELECT *, which gives me the results above. How do I format a statement to retrieve the values - or the entire xml, if that's what I have to do - that I just inserted?



Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: renojim
Premium member *
Comment on: SQL - XML
Posted: 10 years 7 months 28 days 16 hours 18 minutes ago

Nevermind - I get it. The green screen sql session isn't smart enough. Had to use runsql!--script--s in ops nav.