Using SQL Server 2005's XQuery select all nodes with a specific attribute value, or with that attribute missing

Question

Update: giving a much more thorough example.

The first two solutions offered were right along the lines of what I was trying to say not to do. I can't know location, it needs to be able to look at the whole document tree. So a solution along these lines, with /Books/ specified as the context will not work:

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

Original question with better example:

Using SQL Server 2005's XQuery implementation I need to select all nodes in an XML document, just once each and keeping their original structure, but only if they are missing a particular attribute, or that attribute has a specific value (passed in by parameter). The query also has to work on the whole XML document (descendant-or-self axis) rather than selecting at a predefined depth.

That is to say, each individual node will appear in the resultant document only if it and every one of its ancestors are missing the attribute, or have the attribute with a single specific value.

For example:

If this were the 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>A-F</Volume>
      <Volume category="2">G-L</Volume>
      <Volume category="3">M-S</Volume>
      <Volume category="4">T-Z</Volume>
    </Encyclopedia>
  </Encyclopedias>
  <Dictionaries category="1">
    <Dictionary>Webster</Dictionary>
    <Dictionary>Oxford</Dictionary>
  </Dictionaries>
</Library>
    '

A parameter of 1 for category would result in this:

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

A parameter of 2 for category would result in this:

<Library>
  <Novels>
    <Novel category="2">Novel2</Novel>
    <Novel>Novel3</Novel>
  </Novels>
  <Encyclopedias>
    <Encyclopedia>
      <Volume>A-F</Volume>
      <Volume category="2">G-L</Volume>
    </Encyclopedia>
  </Encyclopedias>
</Library>

I know XSLT is perfectly suited for this job, but it's not an option. We have to accomplish this entirely in SQL Server 2005. Any implementations not using XQuery are fine too, as long as it can be done entirely in T-SQL.

Pear

It would probably be best if you gave an example of why this doesn't work. – Certified

Please see my updated answer. I'm curious if this helps you or not. – Kweichow

Answer 1

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: 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 the list of nodes then you can use this expression:

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

but I get the impression that it's not what you need. It would help if you can provide a clearer example.

Edit: This example is indeed more clear. The best that I could find is this:

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

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.

However modify 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:

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

Hope that helps.

Kweichow

I provided a clearer example. Yes, what I need is exactly what XSLT is meant to do, but I can't really use XSLT to do it. We won't be allowed to use a CLR assembly to do the job though, it'll have to be done completely in T-SQL :/ Any other ideas? – Pear

Answer 2

The question is not really clear, but is this what you're 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

Gambetta

No, I tried to say in the question that I can't specify the location in the document, so the //Book part doesn't work. I've edited the question quite a bit to make this more clear. – Pear