Parsing Xml With Unknown Structure In Snowflake

I have a bunch of XML files that define tree hierarchies (relations between ID:s) via nested tags. I want to parse it to tabular format using Snowflake's SQL syntax for semi-struct

Solution 1:

So RECURSIVE is the property on FLATTEN you want to use here:

with data as (
    select parse_xml('<Nodes><NodeId="1"><Nodes><NodeId="2"></Node><NodeId="3"><Nodes><NodeId="4"></Node><NodeId="5"><Nodes><NodeId="6"></Node></Nodes></Node><NodeId="7"></Node></Nodes></Node><NodeId="8"></Node></Nodes></Node><NodeId="9"><Nodes><NodeId="10"></Node></Nodes></Node></Nodes>') as xml
)
select 
    GET(f.value, '@Id') as id
    ,f.path as path
    ,len(path) as p_len
from data,
    TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f
    where get(f.value, '@') = 'Node'
;

gives:

ID  PATH    P_LEN
1[0]32[0]['$']['$'][0]163[0]['$']['$'][1]164[0]['$']['$'][1]['$']['$'][0]295[0]['$']['$'][1]['$']['$'][1]296[0]['$']['$'][1]['$']['$'][1]['$']['$']397[0]['$']['$'][1]['$']['$'][2]298[0]['$']['$'][2]169[1]310[1]['$']['$']13

from this you can now rebuild the hierarchy by find all the matches of path and taking the longest match.

OR

you can do a double nested loop like:

selectGET(f1.value, '@Id') as id
    ,GET(f2.value, '@Id') as id
    ,f1.value
    ,f2.*
    , get(f2.value, '@') 
from data,
    TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f1,
    TABLE(FLATTEN(INPUT=>GET(xmlget(f1.value,'Nodes'), '$'))) f2
    whereget(f1.value, '@') ='Node'
;

BUT it doesn't give you the first row, and snowflake behaves differently with expanding the nodes

<node><nodes><node></node></nodes><node>

and

<node><nodes><node></node><node></node></nodes><node>

which means you have to try handle both which is really gross.

EDIT:

So you can get closer but noting that if the second sub-case happens you can get node name get(f2.value, '@') = 'Node' thus we have something we can stuff into IFF and in the first case, the value of the flatten is 'Node' thus we can hard code fetch the parents -> nodes -> node, thus:

selectGET(f1.value, '@Id') as parent_id
    ,iff(get(f2.value, '@')  ='Node', GET(f2.value, '@Id'), GET(xmlget(xmlget(f1.value,'Nodes'),'Node'), '@Id')) as child_id
from data,
    TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f1,
    TABLE(FLATTEN(INPUT=>GET(xmlget(f1.value,'Nodes'), '$'))) f2
    whereget(f1.value, '@') ='Node'and (get(f2.value, '@')  ='Node'OR f2.value ='Node')
;

gives you:

PARENT_ID   CHILD_ID
1   2
1   3
1   8
3   4
3   5
3   7
5   6
9   10

which is only missing the NULL, 1 and NULL, 9 rows that you wanted.

EDIT 2

So going back to my original suggestion, pulling the node id's and the paths out and then doing a LEFT JOIN on the nodes with a QUALIFY to keep the longest match can be done like so, and gives the desired output:

with data as (
    select parse_xml('<Nodes><NodeId="1"><Nodes><NodeId="2"></Node><NodeId="3"><Nodes><NodeId="4"></Node><NodeId="5"><Nodes><NodeId="6"></Node></Nodes></Node><NodeId="7"></Node></Nodes></Node><NodeId="8"></Node></Nodes></Node><NodeId="9"><Nodes><NodeId="10"></Node></Nodes></Node></Nodes>') as xml
), nodes AS (
select 
    GET(f1.value, '@Id') as id
    ,f1.path as path
    ,len(path) as l_path
from data,
    TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f1
    where get(f1.value, '@') = 'Node'
)
SELECT p.id as parent_id
    ,c.id as child_id
FROM nodes c
LEFT JOIN nodes p
    ON LEFT(c.path,p.l_path) = p.path AND c.id <> p.id
QUALIFY row_number() over (partition by c.id order by p.l_path desc ) = 1
;

gives:

PARENT_ID   CHILD_ID
null112133435563718null9910