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... |
I certainly confess that this is a complex (and possibly not too comprehensible) question - but I would still be glad to get advice...