如何从sql server中的子元素中删除空命名空间

I am trying to delete empty namespace from the child element. I tried with following code but its not deleting

我试图从子元素中删除空命名空间。我尝试使用以下代码，但不删除

SET @xDocTemp.modify('declare default element namespace "mynamepsace"; 
delete /worksh/Data/row[@xmlns=""]')

xml data:

xml数据：

<worksh xmlns="mynamespace">
  <Data>
    <row r="1" ht="18">
    <row xmlns="" rl="39" spans="2">
    <row xmlns="" rl="39" spans="2">
 </Data>
<worksh>

Expected output

预期产出

<worksh xmlns="mynamespace">
  <Data>
    <row rl="1" ht="18">
    <row rl="39" spans="2">
    <row rl="39" spans="2">
 </Data>
<worksh>

1 个解决方案

#1

not sure if it's possible with modify(), but you can just replace it like

不确定是否可以使用modify（），但你可以像它一样替换它

set @xDocTemp = select cast(replace(cast(@xDocTemp as nvarchar(max)), ' xmlns=""', '') as xml)