SQL OpenXML returns nothing when the XML doesn't fill every level

Question

I'm having an issue with OPENXML in SQL Server 2005 where I'll get no results back if the XML doesn't have every tier available. An example would clear up my question:

So we have XML like this:

<Connection Name="DEFAULT" />'
    <Branch Name="A_Branch">
     <Leaf Name="A_Leaf.OP" >
       <Property Name="A_Property" />
     </Leaf>
    </Branch>
</Connection>

And the OPENXML puts into into a table variable like this

INSERT INTO @xmlDataTable
 SELECT *
 FROM OPENXML(@idoc, '/Connection/Branch/Leaf', 2)
  WITH (
     Connection varchar(100) '../../@Name'
   , Branch varchar(100) '../@Name'
   , Leaf varchar(100) '@Name'
   )

And that works fine! But if you put this XML in:

<Connection Name="DEFAULT">
</Connection>

Then the OPENXML returns nothing, an empty row set.

So I'm really not sure what to do to fix that. I need to account for both scenarios, and the scenario with Branches but no leaves. Any thoughts?

Answer 1

If you ask for /Connection/Branch/Leaf then you're going to get exactly what you ask for. The XML snipped you show must return an empty row set, anything else would e incorrect. If you want <connection name="DEFAULT"/> to return something, then ask for /Connection. If you want <connection name="DEFAULT"><branch name="A_Branch"/></connection> to return something, then ask for /Connection/Branch. And so on and so forth.

You can't expect a database to process a query in a manner like 'I couldn't find the row you asked for, but here is something else you may find interesting...'.

If you want /Connection/Branch/Leaf and /Connection/Branch and /Connection then ask for that, use multiple queries and UNION.