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
 

 

issue with XMLTABLE

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
apvardhini

New User


Joined: 23 Apr 2009
Posts: 20
Location: India

PostPosted: Mon Jan 06, 2014 2:12 pm    Post subject: issue with XMLTABLE
Reply with quote

Hi,

I have requirement to parse the incoming XML file and store the information in Db2 tables. I am using XMLTABLE function to achieve this. However I am facing the below issues

1. Lets say I have a XML node as <name></name> and my XPath for this particular column is NAME VARCHAR(10) PATH 'name'. Since I have an empty node, it is returning a blank string to me while I need to use NULL if the string is blank. I understand that Db2 V9 for Z/OS will assign NULL to a particular column only if the XPath is not found in the Row-query expression. My question is "Is there any way to achieve it without extracting the individual string and transforming it?"

2. The above problem is repeated when I use columns with DATE type. But in case of DATE fields, I am getting an error.

Example : <DOB> </DOB> with an XPath of BIRTH_DATE DATE PATh 'DOB' returns an error THE VALUE CANNOT BE CONSTRUCTED AS, OR CAST (USING AN IMPLICIT OR EXPLICIT CAST) TO THE DATA TYPE xs:date. ERROR QNAME=err:FORG0001 with sqlcode -16061

PS: I caannot use XQuery in this regard because of performance issues
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jan 06, 2014 6:47 pm    Post subject:
Reply with quote

this ?:
Code:
BIRTH_DATE DATE PATH 'DOB[string-length(text()) > 0] '
Back to top
View user's profile Send private message
apvardhini

New User


Joined: 23 Apr 2009
Posts: 20
Location: India

PostPosted: Tue Sep 02, 2014 8:06 am    Post subject: XPTY0004 error
Reply with quote

I resolved the issue by extracting the rows and then processing it separately

Now I am facing a different problem

I am trying to process an array of XML elements using XMLTABLE

Lets say my xml is something like below

<ApplicationDet>
<item>
..........
..........
<Services>
<debitcards>
<item>
<name>ABC</name>
<num>123</num>
<seq>1</seq>
</item>
<item>
<name>XYZ</name>
<num>987</num>
<seq>2</seq>
</item>
</debitcards>
</Services>
<seq>1</seq>
</item>
<item>
..........
..........
<Services>
<debitcards>
<item>
<name>SDF</name>
<num>1456</num>
<seq>1</seq>
</item>
<item>
<name>TYU</name>
<num>9033</num>
<seq>2</seq>
</item>
</debitcards>
</Services>
<seq>2</seq>
</item>
</ApplicationDet>

I need to access the Name and num nodes within debitcards for each item within AppplicationDet

One ApplicationDet contains multiple Services and one services contain multiple debitcards.

To retrieve the individual name and num of debitcards, I write my XMLTABLE function as below

SELECT
NAME
,NUM
,SEQ
INTO
V_NAME
,V_NUM
,V_SEQ
FROM XMLTABLE
(
'$I/ApplicationDet/item'
PASSING XMLPARSE (DOCUMENT XMLDOC) AS "I" COLUMNS
NAME VARCHAR(30)
PATH 'Services/item/name'
NUM INTEGER
PATH 'Services/item/num'
SEQ INTEGER
PATH 'Services/item/seq'
SEQ1 INTEGER
PATH 'seq'
) AS Z
WHERE Z.SEQ = X
AND Z.SEQ1 = Y;

where the X and Y are incremented accordingly.

But when I execute the below query, am getting -16003 saying Interger is exepected where item() or item()+ is there, ERROR QNAME = QPTY0004.

Please help me with it as I need to process them individually, transform them and insert each row into a separate table.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Sep 02, 2014 9:39 pm    Post subject:
Reply with quote

This message means that the query is trying to cast an XML sequence of multiple items to a single INTEGER value. A value of data type "(item(), item()+)" means the value is an item followed by one or more additional items. In simpler terms, this means that the value is a sequence of two or more items.

Try any of the following to avoid this error,
1)Return only one of multiple phone numbers
http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_only_one_of_multiple_elements
2)Return a list of multiple phone numbers in a single Varchar value
http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_a_list_of_multiple_values_in_a_single_Varchar
3)Return a list of multiple phone numbers as an XML type
http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_multiple_elements_as_an_XML_sequence
4)Return multiple phone columns
http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_multiple_phone_columns
5)Return one row per phone number
http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_one_row_per_phone_number
Back to top
View user's profile Send private message
apvardhini

New User


Joined: 23 Apr 2009
Posts: 20
Location: India

PostPosted: Fri Sep 05, 2014 9:23 pm    Post subject:
Reply with quote

I get from the examples given, I see that using predicates would be a best solution. I need to know how to use a variable inside a predicate. I gave item[x] or item[y] incrementing X and Y, but it does not seem to work. Is there any specific syntax to it. Your help would be much appreciated. Thanks in advance
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Thu Sep 11, 2014 11:50 pm    Post subject:
Reply with quote

I am not sure if that is allowed or not but you may wants to user either the above approach ( from the links) or you can save all the entries into a temp table instead of array and process it ( I hope I understood you rightly).
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 DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts REXX Screen not working due to LINKED... sundarkudos CLIST & REXX 1 Mon May 09, 2016 1:44 pm
No new posts Issue in sending zip file as mail att... ajithajt JCL & VSAM 8 Thu Apr 07, 2016 9:11 am
No new posts Include Cond issue vickey_dw DFSORT/ICETOOL 6 Tue Mar 29, 2016 8:51 pm


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