SQL Query which deletes an attribute in XML

Question

I have column of XML type.There's a xml like this

<items>
   <item type="xxx"><items>
   <item type="xxx"><items>
</items>

I need to delete all type attributes. I know oracle has some functions for xml manipulation, but I don't get how to delete attributes.

How would such query look like ?

Answer 1

Here is an example using the Oracle supplied SQL function deletexml

Acknowledgement to Jonas Lincoln as I am using his XPATH expression

SELECT deleteXML(xmltype.CREATEXML('<items>
                                       <item type="xxx">a</item>
                                       <item type="xxx">b</item>
                                   </items>'),
                                   '/items/item[@type="xxx"]/@type')
FROM dual  

<items>
   <item>a</item>
   <item>b</item>
</items>                                                         

Answer 2

declare @xml as xml
set @xml = '
<items>
    <item type="xxx">3</item>
    <item type="xxx">4</item>
    </items>'

SET @xml.modify('delete (/items/item[@type="xxx"]/@type)')

select cast(@xml as nvarchar(100))

<items>
  <item>3</item>
  <item>4</item>
</items>