View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
this ?:
Code: |
BIRTH_DATE DATE PATH 'DOB[string-length(text()) > 0] ' |
|
|
Back to top |
|
|
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. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
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 |
|
|
|