That works as long as you do not have COMMIT(*NONE) on your connection.  Otherwise you get SQLSTATE 42926 "Message [SQL0433] LOB and XML locators are not allowed with COMMIT(*NONE)".

On 11/4/2021 1:31 PM, Tim Fathers wrote:
Exactly. I think this ought to work, but I'm not in front of a machine to check it.

insert into vtxtaxid (taxareaid)
select t.*
from json_table (
get_clob_from_file('/vertexCloud/output/xl_0000401.json', 1),
'$.meta' columns (
app char(50) path '$'
) as t

From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Peter Dow<petercdow@xxxxxxxxx>
Sent: 04 November 2021 19:40
To:midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Need help with json_table

I never used json_table before, so I had to look it up. According to the
manual, "json_expression" is not a file name, it's the actual json. The
example in the manual
( gets
the json_expression from a column in a table:

SELECT U."id", U."first name", U."last name", U."phone number" FROM
"firstname" VARCHAR(20), "lastname" VARCHAR(20), "phoneno" VARCHAR(20))
ERROR ON ERROR) AS U Also, the "path" goes with the column, not in the
"strict $". The following works on a v7r3 box:

select t.*
from json_table (
"meta": {
"app": "Vertex REST API v0.1.0",
"timeReceived": "2021-06-22T14:08:07.838Z",
"timeElapsed(ms)": 17
"data": {
"lookupResults": [
{"jurisdictionLevel": "COUNTRY"
{"jurisdictionLevel": "STATE"
{"jurisdictionLevel": "COUNTY"
{"streetAddress1": "2301 Renaissance Blvd Ste 7"
{"lookupResult": "NORMAL"
], "taxAreaId": 390910000,
"asOfDate": "2021-06-18",
"confidenceIndicator": 100
, 'strict $'
COLUMNS ("app" varchar(50) path '$')

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxx /

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support by shopping at with our affiliate link:

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.