Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem with pureXML in DB2 V10

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Problem with pureXML in DB2 V10
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

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Thu Feb 05, 2015 2:42 am    Post subject: Reply to: Problem with pureXML in DB2 V10
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    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 Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us