Menu
I'm trying to parse a piece of XML and remove nodes that contain certain values. I know how to remove them if the value is exact, but I want to use something like a "contains".
This works to delete exactly:
update @XML set data.modify('delete //Message[text() = "customer has deleted their account"]')
But I want to delete where the "Message" node just contains the text, something like:
update @XML set data.modify('delete //Message[contains(text(), "customer")]')
However this returns the error:
XQuery [@XML.data.modify()]: 'contains()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
329
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
To update data in an XML column, use the SQL UPDATE statement. Include a WHERE clause when you want to update specific rows. The entire column value will be replaced. The input to the XML column must be a well-formed XML document.
Try
update @XML set data.modify('delete //Message[text()][contains(.,"customer")]')
to delete the <message/> element and its contents
update @XML set data.modify('delete //Message//text()[contains(.,"customer")]')
to delete the <message/> element's contents.
Example here http://msdn.microsoft.com/en-us/library/ms178026.aspx
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
