I can't delete empty containers in an xml document on SQL Server, such as "<Item />"

Question

I started working for the first time and I don't know much yet, I've been suffering with this problem for two days.

I have a document of this type:

<Tables>
  <dbo.ES_Connection_Modes>
    <Item />
  </dbo.ES_Connection_Modes>
  <dbo.ES_Device_Categories>
    <Item>
      <LINK>1</LINK>
      <F_Class>1</F_Class>
      <N_Code>1</N_Code>
      <B_Default>1</B_Default>
      <B_Meter>1</B_Meter>
      <B_Tranf>0</B_Tranf>
      <B_Regist>0</B_Regist>
      <B_Show_InMenu>1</B_Show_InMenu>
      <N_Project>-1</N_Project>
      <C_Const>EDC_Meter</C_Const>
    </Item>
  </dbo.ES_Device_Categories>
  <dbo.ES_Indicating_Device_Types>
    <Item />
  </dbo.ES_Indicating_Device_Types>
  <dbo.ES_Operating_Principles>
    <Item />
  </dbo.ES_Operating_Principles>
  <dbo.ES_Precission_Classes>
    <Item>
      <LINK>7</LINK>
      <N_Project>687783</N_Project>
      <C_Name>2,0</C_Name>
      <C_Const>EPC_20</C_Const>
    </Item>
    <Item>
      <LINK>8</LINK>
      <N_Project>687783</N_Project>
      <C_Name>2,5</C_Name>
      <C_Const>EPC_25</C_Const>
    </Item>
  </dbo.ES_Precission_Classes>
  <dbo.ES_Granularity>
    <Item />
  </dbo.ES_Granularity>
</Tables>

I need to delete the empty item containers and the container containing it to get a document of the following type:

<Tables>
  <dbo.ES_Device_Categories>
    <Item>
      <LINK>1</LINK>
      <F_Class>1</F_Class>
      <N_Code>1</N_Code>
      <B_Default>1</B_Default>
      <B_Meter>1</B_Meter>
      <B_Tranf>0</B_Tranf>
      <B_Regist>0</B_Regist>
      <B_Show_InMenu>1</B_Show_InMenu>
      <N_Project>-1</N_Project>
      <C_Const>EDC_Meter</C_Const>
    </Item>
  </dbo.ES_Device_Categories>
  <dbo.ES_Precission_Classes>
    <Item>
      <LINK>7</LINK>
      <N_Project>687783</N_Project>
      <C_Name>2,0</C_Name>
      <C_Const>EPC_20</C_Const>
    </Item>
    <Item>
      <LINK>8</LINK>
      <N_Project>687783</N_Project>
      <C_Name>2,5</C_Name>
      <C_Const>EPC_25</C_Const>
    </Item>
  </dbo.ES_Precission_Classes>
</Tables>

I've tried a lot of things, deleted them, but here's the way I was hoping for, but I get an error:

SET @myDoc.modify('delete /Tables/*[contains(name(), "null")]');

Answer 1

Please try the following solution.

It is deleting 2nd level XML elements under the root (Tables) that have in turn Item elements without children.

SQL

DECLARE @myDoc XML =
N'<Tables>
    <dbo.ES_Connection_Modes>
        <Item/>
    </dbo.ES_Connection_Modes>
    <dbo.ES_Device_Categories>
        <Item>
            <LINK>1</LINK>
            <F_Class>1</F_Class>
            <N_Code>1</N_Code>
            <B_Default>1</B_Default>
            <B_Meter>1</B_Meter>
            <B_Tranf>0</B_Tranf>
            <B_Regist>0</B_Regist>
            <B_Show_InMenu>1</B_Show_InMenu>
            <N_Project>-1</N_Project>
            <C_Const>EDC_Meter</C_Const>
        </Item>
    </dbo.ES_Device_Categories>
    <dbo.ES_Indicating_Device_Types>
        <Item/>
    </dbo.ES_Indicating_Device_Types>
    <dbo.ES_Operating_Principles>
        <Item/>
    </dbo.ES_Operating_Principles>
    <dbo.ES_Precission_Classes>
        <Item>
            <LINK>7</LINK>
            <N_Project>687783</N_Project>
            <C_Name>2,0</C_Name>
            <C_Const>EPC_20</C_Const>
        </Item>
        <Item>
            <LINK>8</LINK>
            <N_Project>687783</N_Project>
            <C_Name>2,5</C_Name>
            <C_Const>EPC_25</C_Const>
        </Item>
    </dbo.ES_Precission_Classes>
    <dbo.ES_Granularity>
        <Item/>
    </dbo.ES_Granularity>
</Tables>';

SET @myDoc.modify('delete /Tables/*[not(Item/*)]');

-- test
SELECT @myDoc;