eliran.hayek
New User
Joined: 08 Jan 2015
Posts: 1
Location: israel
|
|
|
|
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 |
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
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. |
|
