Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 2267
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 Having problem passing a variable to ... markzasz JCL & VSAM 12 Thu Jul 11, 2019 5:31 pm
No new posts Pipeline scan problem John Poulakos CICS 1 Thu Feb 28, 2019 10:02 pm
This topic is locked: you cannot edit posts or make replies. Problem while Decompressing the ZIP file Nethraa All Other Mainframe Topics 2 Tue Feb 26, 2019 6:55 am
No new posts Problem while Decompressing the ZIP file Nethraa All Other Mainframe Topics 6 Fri Feb 22, 2019 11:41 pm
No new posts Problem with OVERLAY - BI on SMF Record Ghellar DFSORT/ICETOOL 4 Sat Dec 01, 2018 9:41 pm

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