开发者

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>
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜