issue with XMLTABLE

apvardhini · New User Joined: 23 Apr 2009 Posts: 20 Location: India

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

GuyC · Posted: Mon Jan 06, 2014 6:47 pm

this ?:

apvardhini · New User Joined: 23 Apr 2009 Posts: 20 Location: India

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.

Rohit Umarjikar · Posted: Tue Sep 02, 2014 9:39 pm

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
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
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
www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_multiple_elements_as_an_XML_sequence
4)Return multiple phone columns
www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_multiple_phone_columns
5)Return one row per phone number
www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Return_one_row_per_phone_number

apvardhini · New User Joined: 23 Apr 2009 Posts: 20 Location: India

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

Rohit Umarjikar · Posted: Thu Sep 11, 2014 11:50 pm

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).