IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

issue with XMLTABLE


 
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1281
Location: Belgium

PostPosted: Mon Jan 06, 2014 6:47 pm
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
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

Global Moderator


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

PostPosted: Tue Sep 02, 2014 9:39 pm
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
Use [URL] BBCode for Links
2)Return a list of multiple phone numbers in a single Varchar value
Use [URL] BBCode for Links
3)Return a list of multiple phone numbers as an XML type
Use [URL] BBCode for Links
4)Return multiple phone columns
Use [URL] BBCode for Links
5)Return one row per phone number
Use [URL] BBCode for Links
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
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

Global Moderator


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

PostPosted: Thu Sep 11, 2014 11:50 pm
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 All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
This topic is locked: you cannot edit posts or make replies. issue with Syntax error SYNCSORT 2
No new posts Issue while fetching a column(Nullabl... DB2 18
No new posts Issue while reading a file in browse ... TSO/ISPF 7
No new posts Issue in SFTP a file All Other Mainframe Topics 1
No new posts Issue in KWIKKEY utility while creat... JCL & VSAM 2

Back to Top