Where does a value occur in an XML snippet?

Question

Hi,
I am using using .nodes(),.query() & .value() to return values from inside of my XML documents - this is working fine.

What I would really like to do is know from WHERE in the XML document the value occurred. For example, take the following (very silly) query:

declare @x xml = '	<root>
						<node1>node1</node1>
						<node2>
							<node4>node4</node4>
						</node2>
						<node3>node3</node3>
					</root>';
select	nd.value('.','nvarchar(max)')
from	@x.nodes('//.') X(nd)

Is there a way of finding out exactly where (defined by an XPath) in the original XML document the returned values come from?

Essentially I want to say to my user, "I have found these values in your XML document that might warrant your attention, here is where you can go to find them!"

Hope that makes sense!

thanks
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 1

The easiest way is to use OPENXML and its edge-table format, eg

DECLARE @x XML 
SET @x = '	<root>
	<node1>node1</node1>
	<node2>
	<node4>node4</node4>
	</node2>
	<node3>node3</node3>
</root>';

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @x

SELECT *
FROM OPENXML( @hDoc, '//*', 2 ) 

EXEC sp_xml_removedocument @hDoc

As you may know, OPENXML is demanding on server memory so call sp_xml_removedocument as soon as possible.  It's possible to reproduce this type of thing with ranking functions, eg ROW_NUMBER(), RANK() etc, but none of them are quite as comprehensive as OPENXML's edge table; really a map of the whole XML document with unique ids for each element, attribute and text.

This is the only reason I still use OPENXML.  More here:
http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx