removing a node from xml data type

HI All,

Please consider the folowing data in an xml data type

<Entity name="Category">
  <Column name="Updated" type="datetime2" nullable="1">
    <Properties>
      <Hide />
    </Properties>
  </Column>
</Entity>

I need to remove all Column nodes that have a Hide node descendant resulting in the folowing xml:

<Entity name="Category">
 </Entity>

But I am unable to find the correct xpath syntax to do so.

SET @xmlResult.modify('delete (//Hide/parent()/parent())');
results in an error when trying to alter the stored procedure
XQuery [modify()]: The XQuery syntax '/function()' is not supported

SET @xmlResult.modify('delete (//Hide/../..)');
results in
XQuery [modify()]: Only non-document nodes may be deleted, found 'element(*,xdt:untyped) | document { (element(*,xdt:untyped) ? & text ? & comment ? & processing-instruction ?) * }'

Any ideas?

Regards
LVL 2
DaFouAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Try it this way:
SET @XmlResult =
'<Entity name="Category">
  <Column name="Updated" type="datetime2" nullable="1">
    <Properties>
      <Hide Test="1"/>
    </Properties>
  </Column>
</Entity>'

SET @XmlResult.modify('delete if (count(/Entity/Column/Properties/Hide)>0) then /Entity/Column[1] else ()')
SELECT @XmlResult

Open in new window

0
 
Anthony PerkinsCommented:
Let's try that again:
DECLARE @XmlResult Xml

SET @XmlResult =
'<Entity name="Category">
  <Column name="Updated" type="datetime2" nullable="1">
    <Properties>
      <Hide/>
    </Properties>
  </Column>
</Entity>'

SET @XmlResult.modify('delete if (/Entity/Column/Properties/Hide) then /Entity/Column[1] else ()')
SELECT @XmlResult

Open in new window

0
 
DaFouAuthor Commented:
the first worked as well. thx alot

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.