XML attribute not removing using XML.modify

Question

My XML is like below.

 <TDBUIData xmlns="http://www.somedomain.com/pace/r2.4/v1">
  <FileCreationDate>2014-07-18T13:21:48.507</FileCreationDate>
  <TDBUIDataList xmlns="">
    <PaceJobNumber>3898646894</PaceJobNumber>
    <TaskData>
      <TaskCode>Secon</TaskCode>
      <TaskAssigneeForecastFinishDate>07/16/2014</TaskAssigneeForecastFinishDate>
      <ActualFinishDate>01/01/2000</ActualFinishDate>
    </TaskData>
  </TDBUIDataList>
<TDBUIDataList xmlns="">
    <PaceJobNumber>11331313232</PaceJobNumber>
    <TaskData>
      <TaskCode>Secod</TaskCode>
      <TaskAssigneeForecastFinishDate>07/16/2014</TaskAssigneeForecastFinishDate>
      <ActualFinishDate>01/21/2000</ActualFinishDate>
    </TaskData>
  </TDBUIDataList>
</TDBUIData>

I need to remove xmlns="" attribute from all TDBUIDataList tags.

I am using this query, But its just not working.

DECLARE @XML XML            
;WITH XMLNAMESPACES (DEFAULT 'http://www.somedomain.com/pace/r2.4/v1' )                 
Select @XML=(SELECT  GETDATE() AS FileCreationDate, CONVERT(XML,@paceXML)       
FOR XML PATH('TDBUIData'))

SET @XML.modify('delete (/TDBUIData/TDBUIDataList/@xmlns[.=""])[1]')
SELECT @XML

To start, you'll need to specify the namespace in your delete query. Nothing will match `/TDBUIData` as it is in a different namespace. — podiluska, Jul 18 '14 at 08:33
This is Invalid flagging. provided answer seems to ask different issue and answers provided below are different then question by just reading question .My question was not supposed to mark as duplicate http://stackoverflow.com/questions/5345746/how-to-delete-an-attribute-from-an-xml-variable-in-sql-server-2008 — Mayank Pathak, Jul 18 '14 at 09:15
Correct, Fundamental is the same...But none of their answer provide answer to my question...You could go through all the answers given and then verify that... — Mayank Pathak, Jul 18 '14 at 09:51

XML attribute not removing using XML.modify

0 Answers0