Midrange News for the IBM i Community


Posted by: renojim
SQL - XML
has no ratings.
Published: 20 Sep 2013
Revised: 20 Sep 2013 - 1313 days ago
Last viewed on: 24 Apr 2017 (2119 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)                        
values('<test><V1>123456789</V1><V2>Test                   
Val</V2><V3></V3><PMT><V1>100</V1><V2>test                 
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:

ID      XMLVAL

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:

SELECT V1 FROM TEST.TABLE

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?

 

X

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

COMMENTS

(Sign in to Post a Comment)
Posted by: renojim
Premium member *
Comment on: SQL - XML
Posted: 3 years 7 months 4 days 5 hours 14 minutes ago

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