SQL Query which deletes an attribute in XML
I have col开发者_JAVA百科umn 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 ?
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>
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>
精彩评论