CODEWITHSUNDEEP
sqlxml
Andy
Andy

Reputation: 43

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

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>

Upvotes: 1

Views: 598

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

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>

Upvotes: 1

Mads Hansen
Mads Hansen

Reputation: 66714

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[. = ""]')

Upvotes: 1

Related Questions