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.

友情链接