How to check if a specific xml node is empty & remove it?

userWJyQ 0
Viewed 16
DECLARE @XmlDoc XML

SET @XmlDoc = '<root>
   <emp>
      <name>John</name>
      <salary>22140</salary>
   </emp>
   <emp>
      <name>Walter</name>
      <salary />
   </emp>
   <emp>
      <salary />
   </emp>
</root>'

I want to check if salary node inside emp is empty or not, if its empty delete that node. Below is what I've tried. It deletes the entire emp tag inside of just the salary tag.

SET @XmlDoc.modify('delete //root/emp[salary = ""]')

Current Output:

<root>
  <emp>
    <name>John</name>
    <salary>22140</salary>
  </emp>
</root>

Expected Output:

<root>
  <emp>
      <name>John</name>
      <salary>22140</salary>
  </emp>
  <emp>
      <name>Walter</name>
  </emp>
</root>
xml sql
2 Answers

Your current XPath is addressing the emp element and testing whether or not the salary computed text value is empty. If you want to address the salary element, then change the XPath to add a step, and then test it's own value in the predicate:

SET @XmlDoc.modify('delete //root/emp/salary[. = ""]')
Mads Hansen0

Please try the following solution.

Notable pouints:

  • There is no need to use //. It is very inefficient, and will traverse the entire XML.
  • XPath predicate [not(text())] is a better way to check for an empty text node.

SQL

DECLARE @XmlDoc XML =
N'<root xmlns="w3.org/something/">
   <emp>
      <name>John</name>
      <salary>22140</salary>
   </emp>
   <emp>
      <name>Walter</name>
      <salary />
   </emp>
   <emp>
      <salary />
   </emp>
</root>';

SET @XmlDoc.modify('declare default element namespace "w3.org/something/";
    delete /root/emp/salary[not(text())]');

-- test
SELECT @XmlDoc;

Output

<root xmlns="w3.org/something/">
  <emp>
    <name>John</name>
    <salary>22140</salary>
  </emp>
  <emp>
    <name>Walter</name>
  </emp>
  <emp />
</root>
Yitzhak Khabinsky0
Related