Sigh. That's not the most attractive kind of question title, methinks...
I'm about to use openxml() to import from a typical XML file, and I'm looking how to parse subtrees. Basically, I want to select several nodes with particular entries and all subnotes below them.
In SQL, the tree structure would be designed via a set of FK-related tables (say, named "level_1" to "level_7"), where most child tables can have several rows per parent (i.e. as one-to-many relationships), so I would query something like
select level_4.name, level_5.name, level_7.value from level_4 key join level_5 key join level_6 key join level_7 where level_4.name in ('value_1', 'value_2', 'value_10') order by 1;
Of course, I could import the whole XML nodes into according tables, and then query that in a similar way, but that's not desired here.
Ideally, I would try to use one according openxml() call - but unfortunaley I'm running into several issues:
So the main xpath expression has to focus on the lowest level, if there are one-to-many relationships involved.
Question: If these limits do hold, are the following general guidelines correct?
So, for the hinted sample (assuming the values are designed as elements, not attributes):
select * from openxml(myXmlFile), '//level_7') with (level_4_name long varchar '../../../name', level_5_name long varchar '../../name', level_7_value long varchar 'value') where level_4_name in ('value_1', 'value_2', 'value_10') order by 1;
(The apparent drawback compared to directly filtering via xpath would be the fact that the whole XML file needs to be parsed, and openxml() may return a much bigger interim result set than needed.)
Or am I missing the point here completely?
I guess this is kind of a general question on how to "flatten" XML structures in a way similar to joining tables...