Updating XML data stored in Snowflake in a variant column-CodePudding

I have a requirement where in I need to update XML data stored in a Snowflake table. Can we update an XML document/element stored in a table with column data type as Variant

In the following example, need to update the value "4" to a new value of 400.

create or replace table xml_demo (id integer, object_col object);
insert into xml_demo (id, object_col)
    select 1001,
        parse_xml('<level1> 1X <level2> 2Y <level3> 3X </level3> <level3> 4 </level3> </level2> </level1>');
select object_col,
       xmlget(object_col, 'level2'),
       xmlget(xmlget(object_col, 'level2'), 'level3', 1)
    from xml_demo;

CodePudding user response：

You left too many specifics unspecified, but let's do this.

First we need to specify a way to navigate to the value "4" you want to update. In this XML object, a way to specify this position is ['$',1,'$',2,'$']. This as we navigate the object, we get to the second element (level2), and then to the 3rd element (level3, with the value 4). With 1->2nd, and 2->3rd, in a 0 based array.

We are going to create a JS UDF that gets a XML object in Snowflake, navigates it in said way, and updates the value in that place:

create or replace function stacko_update_xml(X object, A array, NEW_VALUE float)
returns object
language javascript
as $$
var element = X;
// traverse tree to element
for (var i = 0; i < A.length - 1; i  ) {
  element = element[A[i]]
}
element['$'] = NEW_VALUE;
return X;
$$
;

Then to UPDATE the object with the id=1001:

update xml_demo
set object_col = stacko_update_xml(object_col, ['$',1,'$',2,'$'], 400)
where id = 1001;