Multiple XML Paths
Answered
Hi,
we have a JSON-File via Facebook-API.
{
"data": [{
"account_id": "123",
"name": "NAME123",
"id": "456789",
"body": "Very nice things",
"object_story_spec": {
"page_id": "7878273",
"instagram_actor_id": "XYZ",
"link_data": {
"link": "https:\/\/www.Landingpage.de\/",
"message": "See everything.",
"attachment_style": "link",
"call_to_action": {
"type": "SHOP_NOW"
},
"child_attachments": [{
"link": "https:\/\/www.Landingpage1.com",
"image_hash": "c23aaa9988dc51b097ca8f55c3ad96d9",
"name": "Wow",
"description": "Wow.",
"call_to_action": {
"type": "SHOP_NOW"
}
},
{
"link": "https:\/\/www.Landingpage2.com",
"image_hash": "9d42e9532e9d99258dc747b5ab2771bb",
"name": "MoreWow",
"description": "MoreWow.",
"call_to_action": {
"type": "SHOP_NOW"
}
},
{
"link": "https:\/\/www.Landingpage3.com",
"image_hash": "25c310850543d145d102288a9cf09060",
"name": "VeryMuchWow",
"description": "VeryMuchWow.",
"call_to_action": {
"type": "SHOP_NOW"
}
}
}],
"multi_share_end_card": true,
"multi_share_optimized": false
}
},
"object_type": "SHARE"
}]
}
.
As you see there are multiple xml paths in this file.
First normal "data", then "object_story_spec", and as a path in this path "link_data" and "child_attachments".
We are trying to sovle this problem according to this manual:
https://support.datavirtuality.com/hc/en-us/articles/210854723-handle-multi-valued-XML-paths
However, as soon as we try to get to "link_data" we get an "XMLTABLE or XMLQUERY PASSING context item must be an XML value" error.
Our code is as follow:
SELECT
"x.idColumn",
"x.account_id",
"x.name",
"x.id",
"x.body",
"x.object_type",
"o.page_id"/*,
"o.instagram_actor_id",
"l.link",
"l.message",
"l.attachment_style",
"c.link",
"c.image_hash",
"c.name",
"c.description",
"c.type"*/
FROM
(exec "Facebook".invokeHTTP(endpoint=>'https://graph.facebook.com/v3.0/CLIENTID/adcreatives?fields=account_id,name,id,body,object_story_spec,object_type,object_story_id,link_url,object_url',action=>'GET',requestContentType=>'application/json')) w,
XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root/data' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
COLUMNS
"idColumn" FOR ORDINALITY,
"account_id" STRING PATH 'account_id',
"name" STRING PATH 'name',
"id" STRING PATH 'id',
"body" STRING PATH 'body',
"object_type" STRING PATH 'object_type',
"object_story_spec" XML PATH 'object_story_spec')
"x",
XMLTABLE('/object_story_spec' PASSING "x"."object_story_spec"
COLUMNS
"page_id" STRING PATH 'page_id',
"instagram_actor_id" STRING PATH 'instagram_actor_id',
"link_data" XML PATH 'link_data') "o",
XMLTABLE('/link_data' PASSING '"o"."link_data"'
COLUMNS
"link" STRING PATH 'link',
"message" STRING PATH 'message',
"attachment_style" STRING PATH 'attachment_style',
"call_to_action" STRING PATH 'call_to_action',
"child_attachments" XML PATH '.') "l"/*,
XMLTABLE('child_attachmenets' PASSING '"l"."child_attachements"'
COLUMNS
"link" STRING PATH 'link',
"image_hash" STRING PATH 'image_hash',
"name" STRING PATH 'name',
"description" STRING PATH 'description',
"type" STRING PATH 'type'
) "c"*/
Please note the commented lines.
As I cant see the problem maybe one of you guys knows the solution. How can I get all results in one query?
Thanks and BR,
Jonas
-
For the people who are interested in the solution:
When defining "Passing", dont use single quotes. It is not a string, but a object you want to define.
Furthermore, when declaring a XML path, don't write
"link_data" XML PATH 'link_data'
but
"link_data" XML PATH '.'
The path of the table is defined in the next XMLTable
XMLTABLE('/link_data' PASSING '"o"."link_data"'In conclusion the full and correct code is this:
SELECT
"x.idColumn",
"x.account_id",
"x.name",
"x.id",
"x.body",
"x.object_type",
"o.page_id",
"o.instagram_actor_id",
"l.link",
"l.message",
"l.attachment_style",
"l.call_to_action",
"c.link",
"c.image_hash",
"c.name",
"c.description",
"c.type"
FROM
(exec "Facebook".invokeHTTP(endpoint=>'https://graph.facebook.com/v3.0/CREATIVEID/adcreatives?fields=account_id,name,object_id,id,body,object_story_spec,object_type,effective_object_story_id,link_url,object_url',action=>'GET',requestContentType=>'application/json')) w,
XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root/data' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
COLUMNS
"idColumn" FOR ORDINALITY,
"account_id" STRING PATH 'account_id',
"name" STRING PATH 'name',
"id" STRING PATH 'id',
"body" STRING PATH 'body',
"object_type" STRING PATH 'object_type',
"object_story_spec" XML PATH '.')
"x",
XMLTABLE('object_story_spec' PASSING "x"."object_story_spec"
COLUMNS
"page_id" STRING PATH 'page_id',
"instagram_actor_id" STRING PATH 'instagram_actor_id',
"link_data" XML PATH '.') "o",
XMLTABLE('link_data' PASSING "o"."link_data"
COLUMNS
"link" STRING PATH 'link',
"message" STRING PATH 'message',
"attachment_style" STRING PATH 'attachment_style',
"call_to_action" STRING PATH 'call_to_action',
"child_attachments" XML PATH '.') "l",
XMLTABLE('child_attachments' PASSING "l"."child_attachments"
COLUMNS
"link" STRING PATH 'link',
"image_hash" STRING PATH 'image_hash',
"name" STRING PATH 'name',
"description" STRING PATH 'description',
"type" STRING PATH 'call_to_action/type'
) "c"
;;
Please sign in to leave a comment.
Comments
2 comments