IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Problem with pureXML in DB2 V10


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
eliran.hayek

New User


Joined: 08 Jan 2015
Posts: 1
Location: israel

PostPosted: Fri Jan 09, 2015 1:24 pm
Reply with quote

Hello to all DB2 experts,

I try to develop a dynamic table, but I ran into some problems, I'm sure you can help me.

First of all - I'll tell what I did and what I was successful (on DB2 V10 - Z/OS).

1. I've build a database + tablespace with UNICODE encoding.

2. I set up a new table with a field of type XML, and i've set the parameter "CCSID UNICODE" for my table.

3. I managed to set the table index.

Code:
CREATE INDEX SCHEMA.INDEX_NAME                               
       ON SCHEMA.TABLE_NAME    (XML_COLUMN)                       
       GENERATE KEYS USING XMLPATTERN '/TAG1/TAG2' AS SQL VARCHAR(50) ;


4. I was able to add rows to a table.


Code:
INSERT INTO SCHEMA.TABLE_NAME   
           (XML_COLUMN)           
VALUES                                 
           (XMLPARSE(                       
           '<?xml version="1.0" encoding="UTF-8" ?>
            <TAG1><TAG2>SOME_VALUE</TAG2></TAG1>') )  ;   


5. I managed to pull data from XML.


Code:
SELECT TAG2_VALUE , TAG3_VALUE
   FROM SCHEMA.TABLE_NAME   
          , XMLTABLE('$XML/TAG1'                   
            PASSING XML_COLUMN as "XML"         
            COLUMNS                           
                             " TAG2_VALUE" CHAR(20) PATH 'TAG2'
                           , " TAG3_VALUE" CHAR(20) PATH 'TAG3'
           ) AS XML                             
 where TAG2_VALUE = 'SOME_VALUE'  ;   




Unfortunately, I could not do the following:


1. Even that I wrote in my insert command:

Code:
<?xml version="1.0" encoding="UTF-8" ?>


my data stored as follows:

Code:
<?xml version="1.0" encoding="IBM424"?>



2. This is the most annoying,

I failed to modify the XML, when i tried the next sql :



Code:
UPDATE SCHEMA.TABLE_NAME   
      SET XML_COLUMN = XMLMODIFY(                                 
             'replace value of node /TAG1/TAG2 with "TEST"' )         
WHERE                                                     
      XMLCAST(XMLQUERY('TAG1/TAG2' PASSING XML_COLUMN) AS CHAR(50))
      =  'SOME_VALUE'  ;                   


I get the error :


Code:
DSNT408I SQLCODE = -4730, ERROR:  INVALID SPECIFICATION OF XML COLUMN       
         TABLE_NAME.XML_COLUMN IS NOT DEFINED IN THE XML VERSIONING FORMAT,
         REASON 1                                     
SQLSTATE   = 55079 SQLSTATE RETURN CODE 


Even when I try to add a new node I get the same error.



I would love to get some help,

Thank you all.

De-imaged
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2672
Location: NYC,USA

PostPosted: Thu Feb 05, 2015 2:42 am
Reply with quote

Wlcome eliran.hayek!!

See if below info helps also XMLMODIFY must adhare to xml patteren/XMLMODIFY function require XML multi-versioning or if you have already got a solution then please let us know otherwise check this one.

1 The target of the SET assignment clause containing the XMLMODIFY function invocation is a column that is not XML in the versioning format. The target of an assignment involving an invocation of the XMLMODIFY function must be an XML column that is in the XML versioning format.
2 A SELECT FROM UPDATE or SELECT FROM DELETE statement specified OLD TABLE, and referenced an XML column that is not defined in the XML versioning format.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts problem in select max when executing ... IMS DB/DC 6
No new posts Sysncsort - Justification problem SYNCSORT 6
No new posts Problem getting record length of a re... COBOL Programming 2
No new posts Problem while trying to manipulate re... DFSORT/ICETOOL 3
No new posts TSO IOF problem TSO/ISPF 5
Search our Forums:

Back to Top