Using SQL Server 2005 XQuery, select all nodes with a specific attribute value or with no attribute

Question

Update: a more detailed example.

The first two proposed solutions were correct in accordance with what I tried to say not to do. I can’t know the location; it should be able to look at the entire document tree. Thus, the solution in these lines with / Books /, set as context, will not work:

SELECT x.query('.') FROM @xml.nodes('/Books/*[not(@ID) or @ID = 5]') x1(x) 

Original question with best example:

Using the implementation of SQL Server 2005 XQuery, I need to select all the nodes in the XML document only once and keep their original structure, but only if they do not have a specific attribute or this attribute has a specific value (passed to the parameter). The request should also work on the entire XML document (the axis of the child or on its own), and not at a given depth.

That is, each individual node will be displayed in the resulting document only if it and each of its ancestors lack an attribute or have an attribute with a single specific value.

For example:

If it was XML:

  DECLARE @Xml XML SET @Xml = N' <Library> <Novels> <Novel category="1">Novel1</Novel> <Novel category="2">Novel2</Novel> <Novel>Novel3</Novel> <Novel category="4">Novel4</Novel> </Novels> <Encyclopedias> <Encyclopedia> <Volume>AF</Volume> <Volume category="2">GL</Volume> <Volume category="3">MS</Volume> <Volume category="4">TZ</Volume> </Encyclopedia> </Encyclopedias> <Dictionaries category="1"> <Dictionary>Webster</Dictionary> <Dictionary>Oxford</Dictionary> </Dictionaries> </Library> ' 

Parameter 1 for the category will result in the following:

 <Library> <Novels> <Novel category="1">Novel1</Novel> <Novel>Novel3</Novel> </Novels> <Encyclopedias> <Encyclopedia> <Volume>AF</Volume> </Encyclopedia> </Encyclopedias> <Dictionaries category="1"> <Dictionary>Webster</Dictionary> <Dictionary>Oxford</Dictionary> </Dictionaries> </Library> 

Parameter 2 for the category will result in the following:

 <Library> <Novels> <Novel category="2">Novel2</Novel> <Novel>Novel3</Novel> </Novels> <Encyclopedias> <Encyclopedia> <Volume>AF</Volume> <Volume category="2">GL</Volume> </Encyclopedia> </Encyclopedias> </Library> 

I know XSLT is great for this job, but it is not an option. We have to do this all the way in SQL Server 2005. Any implementations that don't use XQuery are great too, if you can do it all in T-SQL.

Answer 1

It is not clear to me from your example what you are actually trying to achieve. Do you want to return the new XML with all nodes removed except those that satisfy the condition? If so, then this looks like an XSLT conversion job, which I don’t think is built into MSSQL 2005 (can be added as UDF: http://www.topxml.com/rbnews/SQLXML/re-23872_Performing-XSLT- Transforms-on-XML-Data-Stored-in-SQL-Server-2005.aspx ).

If you just need to return a list of nodes, you can use this expression:

 //Book[not(@ID) or @ID = 5] 

but I get the impression that this is not what you need. This will help if you can provide a clearer example.

Change This example is really clearer. The best I could find was this:

 SET @Xml.modify('delete(//*[@category!=1])') SELECT @Xml 

The idea is to remove all nodes from XML that you don’t need, so you are left with the original structure and necessary nodes. I checked your two examples and it gave the desired result.

However, the modification has some limitations - it seems that you cannot use it in the select statement, it must modify the data in place. If you need to return such data using select, you can use a temporary table where you need to copy the original data, and then update this table. Something like that:

 INSERT INTO #temp VALUES(@Xml) UPDATE #temp SET data.modify('delete(//*[@category!=2])') 

Hope this helps.

Answer 2

The question is not entirely clear, but is that what you are looking for?

 DECLARE @Xml AS XML SET @Xml = N' <Books> <Book ID="1">Book1</Book> <Book ID="2">Book2</Book> <Book ID="3">Book3</Book> <Book>Book4</Book> <Book ID="5">Book5</Book> <Book ID="6">Book6</Book> <Book>Book7</Book> <Book ID="8">Book8</Book> </Books> ' DECLARE @BookID AS INT SET @BookID = 5 DECLARE @Result AS XML SET @result = (SELECT @xml.query('//Book[not(@ID) or @ID = sql:variable("@BookID")]')) SELECT @result