All,
Playing aroudn with XMLTABLE (and HttpGetClob())...
Surprisingly, my initial attempt worked :)
-------------- Result Set Columns -------------
COLUMNS
title varchar(100) PATH 'title'
, description varchar(1000) PATH 'description'
, link varchar(500) path 'link'
So I added another column
-------------- Result Set Columns -------------
COLUMNS
title varchar(100) PATH 'title'
, description varchar(1000) PATH 'description'
, link varchar(500) path 'link'
, category varchar(100) path 'category'
But that fails with an error
SQLSTATE 10507
Vendor code -16003
Message: [SQ16003] An XPath expression has a type that is not valid for the
context with which the expression occurs.
Cause: An expression of data type (item90, item()+) cannot be used when the
data type item() is expected for the context.
<...>
Error qName=err:XPTY0004
Luckily, I'd previously read this page
https://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/
and realized that the problem was that there could be more than one
category.
From that solutions on that page, specifying multiple columns worked:
-------------- Result Set Columns -------------
COLUMNS
title varchar(100) PATH 'title'
, description varchar(1000) PATH 'description'
, link varchar(200) path 'link'
, category1 varchar(100) path 'category[1]'
, category2 varchar(100) path 'category[2]'
, category3 varchar(100) path 'category[3]'
, category4 varchar(100) path 'category[4]'
, category5 varchar(100) path 'category[5]'
) nf
But trying to return a joined string does not
-------------- Result Set Columns -------------
COLUMNS
title varchar(100) PATH 'title'
, description varchar(1000) PATH 'description'
, link varchar(200) path 'link'
, categories varchar(1000) path 'fn:string-join(category/text(),",")'
I get a message
SQL State: 10506
Vendor Code: -16009
Message [SQ16009]: An XPath functio named fn:string-join is not defined in
the static context.
Cause: In a call to function fn:string-join with 2 parameters, the expanded
QName and number of parameters does not match the function name and number
of parameters of a function signature in the state contaxt.
Error QName=err:XPST0017
Also tried
-------------- Result Set Columns -------------
COLUMNS
title varchar(100) PATH 'title'
, description varchar(1000) PATH 'description'
, link varchar(200) path 'link'
, categories varchar(1000) path 'fn:string-join(category,",")'
, category varchar(100) path 'category'
) nf
Same error..
Can anybody tell me what I'm doing wrong?
Thanks!
As an Amazon Associate we earn from qualifying purchases.