Maintaining Ancestry with XPath in SQL Server

Question

I have an XML file that uses the attribute "action" to denote a change from a previous file.  Here is an example of my XML:

<Ancestor1>
  <Ancestor2>
    <Ancestor3>
      <Parent>
        <Sibling>
          <Name>Tom</Name>
        </Sibling>
        <Sibling action="update">
          <Name>Matt</Name>
        </Sibling>
        <Sibling>
          <Name>Harry</Name>
        </Sibling>
        <Sibling action="update">
          <Name>Frank</Name>
        </Sibling>        
      </Parent>
    </Ancestor3>
  </Ancestor2>
</Ancestor1>

I would like to write an XPath filter that selects only the sibling elements with the 'action="update"' attribute, yet still retain the ancestor hierarchy:

<Ancestor1>
  <Ancestor2>
    <Ancestor3>
      <Parent>
        <Sibling action="update">
          <Name>Matt</Name>
        </Sibling>
        <Sibling action="update">
          <Name>Frank</Name>
        </Sibling>        
      </Parent>
    </Ancestor3>
  </Ancestor2>
</Ancestor1>

I am able to successfully filter using the following expression './*//*[@action=''update'']' but this does not retain the ancestry.  Is there a way to select all "action" attributes and retain ancestry up to the root node?

BTW:  I am using Xquery in SQL Server 2008 so the "ancestor" axis is not supported.

Answer 1

What is your overall goal doing this in T-SQL?

Imho the only thing you can do is:

SELECT	S.Data.value('.', 'NVARCHAR(255)') AS [Ancestor3/Parent/Sibling/Name]
FROM	@Data.nodes('/Ancestor1/Ancestor2/Ancestor3/Parent/Sibling[@action="update"]/Name') S ( Data )
		FOR XML PATH('Ancestor2'), ROOT('Ancestor1');

Answer 2

You can't really do that with the SQL Server implementation of XQuery.  Even in a fuller implementation of XQuery you would have to write a loop, or use XSLT to transform it. 

What you can do in SQL Server is store the XML in a variable and use the DML extensions ( eg delete, insert, replace value of ) to edit the XML and get the same effect, eg

DECLARE @xml XML = '<Ancestor1>
  <Ancestor2>
    <Ancestor3>
      <Parent>
        <Sibling>
          <Name>Tom</Name>
        </Sibling>
        <Sibling action="update">
          <Name>Matt</Name>
        </Sibling>
        <Sibling>
          <Name>Harry</Name>
        </Sibling>
        <Sibling action="update">
          <Name>Frank</Name>
        </Sibling>        
      </Parent>
    </Ancestor3>
  </Ancestor2>
</Ancestor1>'

SELECT 'before' s, DATALENGTH(@xml) dl, @xml

SET @xml.modify('delete //Sibling[not(@action)]')

SELECT 'after' s, DATALENGTH(@xml) dl, @xml

 

Answer 3

Hi Stefan,

I am receiving XML files that contain partial updates for my existing database (emphasis on partial).  I used Xquery to populate my database, but now I am receiving XML files with changes to the existing data.  My thinking is that filtering down to a specific expression (e.g. "Add", "Update", "Delete") will allow me to simplify the update and delete queries I plan on writing for each table in my database.

I am new to pushing XML data into database tables though.  Perhaps there is an easier method to update my DB data that I am overlooking?

Eric

Answer 4

Is Bob's solution meet your requirement? Eric?

Answer 5

Then plain parsing with filtering is should be sufficient, as the path it self does not provide that much information:

DECLARE @Data XML = N'
<Ancestor1>
  <Ancestor2>
    <Ancestor3>
      <Parent>
        <Sibling>
          <Name>Tom</Name>
        </Sibling>
        <Sibling action="update">
          <Name>Matt</Name>
        </Sibling>
        <Sibling>
          <Name>Harry</Name>
        </Sibling>
        <Sibling action="update">
          <Name>Frank</Name>
        </Sibling>        
      </Parent>
    </Ancestor3>
  </Ancestor2>
</Ancestor1>
';

SELECT  A1.n.query('.') ,
        A2.n.query('.') ,
        A3.n.query('.') ,
        P.n.query('.') ,
        S.n.query('.')
FROM    @Data.nodes('/Ancestor1') A1 ( n )
        CROSS APPLY A1.n.nodes('Ancestor2') A2 ( n )
        CROSS APPLY A2.n.nodes('Ancestor3') A3 ( n )
        CROSS APPLY A3.n.nodes('Parent') P ( n )
        CROSS APPLY P.n.nodes('Sibling[@action="update"]') S ( n ) ;

You need to extract the necessary values from this construct. Then you can update your data. btw, maybe you should also take a look at the MERGE statement.

Answer 6

Thank you Stefan.  You're sample query has given me some new ideas to work with.