StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It's not clear for me from your example what you're actually trying to achieve. Do you want to return a new XML with all the nodes stripped out except those that fulfill the condition? If yes, then this looks like the job for an XSLT transform which I don't think it's built-in in MSSQL 2005 (can be added as a UDF: <a href="http://www.topxml.com/rbnews/SQLXML/re-23872_Performing-XSLT-Transforms-on-XML-Data-Stored-in-SQL-Server-2005.aspx" rel="nofollow noreferrer">http://www.topxml.com/rbnews/SQLXML/re-23872_Performing-XSLT-Transforms-on-XML-Data-Stored-in-SQL-Server-2005.aspx</a>).</p> <p>If you just need to return the list of nodes then you can use this expression:</p> <pre><code>//Book[not(@ID) or @ID = 5] </code></pre> <p>but I get the impression that it's not what you need. It would help if you can provide a clearer example.</p> <p><strong>Edit</strong>: This example is indeed more clear. The best that I could find is this:</p> <pre><code>SET @Xml.modify('delete(//*[@category!=1])') SELECT @Xml </code></pre> <p>The idea is to delete from the XML all the nodes that you don't need, so you remain with the original structure and the needed nodes. I tested with your two examples and it produced the wanted result.</p> <p>However <em>modify</em> has some restrictions - it seems you can't use it in a select statement, it has to modify data in place. If you need to return such data with a select you could use a temporary table in which to copy the original data and then update that table. Something like this:</p> <pre><code>INSERT INTO #temp VALUES(@Xml) UPDATE #temp SET data.modify('delete(//*[@category!=2])') </code></pre> <p>Hope that helps.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload