June 21, 2006 at 12:32 pm
When you have several elements/nodes, can the OPENXML function be used to flatten out the entire element/node structure under a root? It appears that when specifying the rowpattern you can not go into another rowpattern using the ColPattern syntax (i.e. go up two nodes, then down into another node). If so, can someone help with the syntax or is there another method of producing one result set with an entire XML document flattened? Or, are you forced to use a UNION?
June 21, 2006 at 3:06 pm
Hi Chase..
A technique called "parent axis access" can help you here. You drill down to the deepest layer of the XML and then use ".." parent references to jump back up to the elements of interest. Here's a trivial sample:
select distinct Component, Build, Description
from OPENXML (@idoc, '/HDDU/Build/ThisBuild', 1) with
(Component nvarchar(255) '../@Component',
Build nvarchar(255) '@BuildNumber',
Description nvarchar(255) '../@Description')
If you're using SQL Server 2005, you'll get better performance and maintainability if you use the new NODES() syntax and CROSS APPLY operator. There's a sample here.
Hope this helps!
- Ward Pond
blogs.technet.com/wardpond
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply