asked on

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

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

DaFou

ASKER

the first worked as well. thx alot

Regards